Ordering steps in a SQL Agent job

2

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.

JobStepsControl1

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.

JobStepsControl2

Next, for each of the other tabs, we go into the Advanced tab of the job edit screen.

JobStepsControl3

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.

JobStepsControl4

In our case we want Go to step: [6] Error Handling.

JobStepsControl5

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.

JobStepsControl6

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.

JobStepsControl7

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.

JobStepsControl8

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 )

Google+ photo

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

Connecting to %s

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

Join 1,674 other followers

Follow me on Twitter

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