Run Two Sql Server Agent Jobs in Parallel and Run a Third Job at the end of both

Question

I have two SSIS ETL packages that I need to schedule to run on a daily basis. The two packages load data into two different staging databases so these can be run in parallel. However, at the end of execution of both the jobs, I need to call a separate job (stored procs) to load data into final database from the staging database.

Does SQL Server Job Scheduling Agent provide any features for tracking if the previous two jobs were completed successfully or not?

Any help is highly appreciated.

Thanks!

Answer 1

I would recommend that you include a third 'control' package in your ssis project that contains Execute Package tasks to run the two packages in parallel within a Sequence Container, and then an Execute SQL task following the successful completion of the Sequence Container to kick off the stored procedure once these both complete.

Doing this, you only need to have one Agent job that runs the 'control' package.

Answer 2

Your best bet is to Wrap your 2 SSIS packages in a Master Package - These can be run in parallel within this. Then create a Job with this as step 1 Step 2 can be Exec sp_run_job [job you need to run]