Generally if I need to do any form of complicated control flow I’ll end up using SSIS, however there is also a limited amount of control flow available in SQL Agent jobs. Here are a couple of examples of how it works and what you can do with it.
First here is a very basic job with 5 steps.
The important columns here are On Success and On Failure. These tell the job where to go after the step completes. The default for a successful completion is to Go to the next step and for failure is to Quit the job reporting failure, and these are what you want 90% of the time. However, let’s say I want to use an error handling step.
First we add the Error Handling step.
Next, for each of the other tabs, we go into the Advanced tab of the job edit screen.
If you look in the drop down list under On failure action you will see each of the steps numbered with their names as well as Go on to next step, Quit the job reporting success and the default Quit the job reporting failure.
In our case we want Go to step: [6] Error Handling.
So now if any of our 5 steps fail they go immediately to our step Error Handling.
Next let’s create a loop. I’m going to rename step 2 as Load Data and as long as it succeeds I’m going to want it to keep re-running. Once it fails I want to go on to the next step.
Now when we look at the drop down for On success action you will notice that Load Data isn’t an option so we can’t just loop directly back. So I’m going to use Step 3 as my Looper step. The Looper step doesn’t have to do anything. It just has to always succeed.
Once we change the On Success option for steps 2 and 3 to be each other we have a loop, but how do we get to Step 4? Well, when the Load Data step fails we will go directly to Step 4 and skip the Looper step. This does mean that we have to deal with any unexpected errors separately, but we can handle that in code if needed.
Hopefully this gives a good enough feel for how the control flow of a SQL Server Agent job works that you will be able to handle what comes along. Or at least understand what the guy before you did anyway!
Filed under: Microsoft SQL Server, SQL Agent Jobs, SQLServerPedia Syndication, SSMS Tagged: control flow, microsoft sql server, SQL Agent Jobs, SSMS 
