How Do I Check Job Status From SSIS Control Flow?

Question

Here's my scenario - I have an SSIS job that depends on another prior SSIS job to run. I need to be able to check the first job's status before I kick off the second one. It's not feasible to add the 2nd job into the workflow of the first one, as it is already way too complex. I want to be able to check the first job's status (Failed, Successful, Currently Executing) from the second one's, and use this as a condition to decide whether the second one should run, or wait for a retry. I know this can be done by querying the MSDB database on the SQL Server running the job. I'm wondering of there is an easier way, such as possibly using the WMI Data Reader Task? Anyone had this experience?

Answer 1

You may want to create a third package the runs packageA and then packageB. The third package would only contain two execute package tasks.

http://msdn.microsoft.com/en-us/library/ms137609.aspx

@Craig A status table is an option but you will have to keep monitoring it.

Here is an article about events in SSIS for you original question.
http://www.databasejournal.com/features/mssql/article.php/3558006

Answer 2

Why not use a table? Just have the first job update the table with it's status. The second job can use the table to check the status. That should do the trick if I am reading the question correctly. The table would (should) only have one row so it won't kill performance and shouldn't cause any deadlocking (of course, now that I write it, it will happen) :)

@Jason: Yeah, you could monitor it or you could have a trigger start the second job when the end status is recieved. :)

Answer 3

@Jason, Thanks for the links. I guess my best option will be to abstract the jobs out into a master job that controls them.