Run SQL Agent Job B Based on the Outcome of Job A with a slight twist

Question

Run SQL Agent Job B Based on the Outcome of Job A with a slight twist

mark.humphreys

SSC Eights!

Points: 887
More actions
August 9, 2018 at 9:35 am

#375608

Hello Everyone,
I am looking for some help please. Allow me to explain the scenario.
I have a SQL agent job which has multiple steps to extract data from our source systems this runs at a schedule time and it takes approx. 6 hours. I have a second job which runs at a set time in the early of the morning which does a further data extract from a second system. The reason for this is because we do not get a copy of production data until a certain time.
The jobs used to be combined, however as just mentioned the second system data extract requires the job to run at a different time.
I have been researching ways to make the second job run based on the successful outcome of the first job. This is clearly possible by adding a step into the first job, however I do not wish the second job to be dependent in that way to the first.
What I would like is the first step of the second job to test if the outcome of the first job and if successful then start, if not fail the second job and not run. Again I know I can test sysjobs and sysjobhistory for the last outcome. However I am struggling with the part that does the test when the second job runs.
Many thanks in advance.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Have the first step of the second job test sysjobhistory, and if the first job failed, raise an error. Set the second job to fail if the first step fails.

John

mark.humphreys SSC Eights! Points: 887 More actions · Answer 2

Thanks John, I will work on that. I appreciate the quick response.

Mark

Steve Jones - SSC Editor SSC Guru Points: 738683 More actions · Answer 3

I actually dislike checking history. What I'd do is have the first job set a semaphore somewhere. Use a small table, mark that the job completed and when. Then have the second job check this before proceeding. Nice thing here is you could have the second job running every minute, looking for a completion on that date (hour, day, etc.) before executing.

mark.humphreys SSC Eights! Points: 887 More actions · Answer 4

Thanks for that Steve. I had seen another article which outlined a similar response. The answers provided have given me a way forward. Much appreciated.