1

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ash
  • 1,180
  • 3
  • 22
  • 36
  • 1
    You can use `sp_start_job` in a sql step to start the next job – Nick.Mc Apr 12 '17 at 23:32
  • You could keep the 2 jobs you have as separate and remove the schedule. This would be for when you want to run each one individually. Create a 3rd job with a schedule that has one job as step 1 and the other as step 2. This will keep the dependency of when the jobs are scheduled. – Jason Apr 12 '17 at 23:37
  • 2
    If you were to add `sp_start_job` in the code for one job, wouldn't that start the 2nd job on the occasion you only want to run one? – Jason Apr 12 '17 at 23:40

1 Answers1

1

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.

Jason
  • 945
  • 1
  • 9
  • 17
  • 1
    Hi Jason, thank you for the reply. I'm considering this workaround. Nonetheless, am interested to know if there is a way to create dependency between Jobs. – Ash Apr 13 '17 at 00:27