Search code examples
sql-serversql-server-agentsql-server-administration

SQL Server Agent job dependency (not step)

We have 2 Jobs created in SQL Server Agent.

  1. PreLoad
  2. DWHLoad

Job step list in both Jobs have various steps.

DWHLoad needs to be run after successful completion of PreLoad Job.

As of now, I've scheduled PreLoad to run at 1:00AM and it finishes at 5:00AM. DWHLoad to run at 6:00AM to avoid issues if PreLoad delays for any reason.

I could gather PreLoad steps into DWHLoad and run as one job to maintain dependency.

However, there are occasions where I need to run PreLoad separately and same is true with DWHLoad.

Is there a way to create dependency on Job and not on Job step?

i.e. Start DWHLoad only after successful completion of the PreLoad job?

enter image description here

Solution

  • Keep the 2 jobs you have and remove the schedule. This will allow you to right click and start the job for the times you want to run them manually. You mentioned that each job has multiple steps, so you will need to create a 3rd job with the combined steps from each job in the order you need. Add a schedule to the 3rd job and you will have the dependency you are wanting with a scheduled job.