Ordering steps in a SQL Agent job


July 8, 2013 by Kenneth Fisher

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!

2 thoughts on “Ordering steps in a SQL Agent job

  1. David Sumlin says:

    Nice job! I hadn’t ever thought of putting a loop into an agent job. Nice little trick!

    • Thanks! Oddly enough I had never used it before, and now that I just wrote about it I’m having to use it at work for something 🙂 Funny how that happens sometimes.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,753 other subscribers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: