How to Create a Data Provider Command Package to Wait for Another Job to Finish

Creating a Data Provider Command Package to Wait for Another Job to Finish

When working with data processes in TimeXtender Data Orchestrator, there are instances when you may need to wait for an unrelated job to complete before initiating another process. In such cases, you can create a Data Provider Command package that ensures the second process starts only after the first job finishes.

Step-by-Step Guide

1. Create a Package > Data Provider Command
2. Select the Data Governance Data Provider and Custom SQL

Custom SQL Command

Use the following SQL command to create the wait mechanism:

DECLARE @task_id int = 123 -- the task id of the schedule group
DECLARE @task_type varchar(20) = 'EBI_PACKAGE' -- the type of the object you are running within the schedule group, as we cannot wait for schedule groups

DECLARE @end_date DATETIME

WHILE 1=1
BEGIN
    SELECT @end_date = end_date
    FROM reporting.execution
    WHERE task_id = @task_id and task_type=@task_type

    IF @end_date IS NOT NULL
    BEGIN
        PRINT 'End date found: ' + CONVERT(VARCHAR, @end_date, 120)
        BREAK
    END

    PRINT 'End date is still NULL. Waiting for 1 minute...'
    WAITFOR DELAY '00:01:00'
END

Instruction

Once you have created the Data Provider Command package with the above script, you need to place this package before the process that needs to wait for the other job to complete.

 

Additional Configuration

Using the Timeout option in this Data Provider Command package can help manage scenarios where the other job gets stuck or frozen. For example, setting a Timeout of 30 minutes ensures the process will wait only up to 30 minutes before failing if the other job is still running.

1. Navigate to the Data Provider Command package settings.
2. Set the Timeout option to the desired duration (e.g., 30 minutes).

Conclusion

Implementing a Data Provider Command package to wait for another job to finish is a useful technique for managing dependencies and ensuring the correct order of execution in your TimeXtender environment. By following the steps outlined above, you can achieve seamless synchronization between your processes.