sqlsql-serverdatastage

How to trigger a DataStage job once a SQL server job has completed?

Currently there is a job in SQL server that is scheduled to runs several times a day.

The SQL Job builds a file for the DataStage job that is scheduled shortly after the SQL server job, problem being that if the SQL job hasn't finished building the file, the DataStage job fails.

Is there a trigger/query that can be added on SQL server to trigger DataStage job?

Solution

  • Multiple solutions are possible:

    1. Start the DataStage job from command line (via dsjob) and schedule this job in the same scheduler (as the SQL server job) after that job (or schedule both in another common scheduler)
    2. Use a wait for file stage in the DataStage Sequence. This could be configured to wait some time for the file.
    3. Trigger the execution from a database trigger - I have done it for Db2 but it should be possible for you as well - maybe you have to write some code for that...