CODEWITHSUNDEEP
sqlsql-serverdatastage
DarrenB
DarrenB

Reputation: 75

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?

Upvotes: 0

Views: 897

Answers (2)

Ray Wurlod
Ray Wurlod

Reputation: 851

Have the SQL Server job create two files, the real file then a second "trigger" file. It can even be empty. DataStage detects the existence of the trigger file, which was not created until the real file was complete, then processes the real file.

Upvotes: 0

MichaelTiefenbacher
MichaelTiefenbacher

Reputation: 4005

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...

Upvotes: 1

Related Questions