Using Error (and other multiple) Paths in SSIS

SSIS provides for multiple paths between tasks.  Very helpful stuff; here we’ll look at a simple solution to a common scenario.
Below is a job that stops a blocking service, reads from the source DB (SQL CE) file, truncates the destination table, and then copies the data from the source to destination.  Finally, it starts the service again.

What if, however, our test job fails after stopping the service?  We can provide for failure notification through SQL Agent jobs, but wouldn’t it be nice, also, to have the service start again, even after a failure?
We’ll set that up using failure paths, such that the tasks will go directly to the start service task in the event of a failure.


First, we want to highlight the rest read task.  When we do, we have the option of creating another job path (highlighted below).

We’ll drag this new path to the start service task:

Now we’ve got two paths from the test read task, but they’re both success paths.  To change that, double-click on the green arrow between the test read and the start service paths.  When you do, you’ll see a window like the one below, showing the defaults:

We need to change a couple of things here.  First, we want this to be a path for failures, so change the success value field to “failure.”
Next, we want to change this to an OR operation.  SSIS allows you to say, “only proceed to this next step if all of the conditions pointing to it are true.”  That is, you can have multiple branches in a job that converge on a single task, and that task will not fire until all of the previous tasks have completed successfully (or not; your choice).  So it’s really powerful stuff.  In our case, we’re going for simple:  we will only have a single path to the final task in any given execution, which is the definition of an OR.

Now, once we click on OK, we’ll notice a couple of things.  First, the path from the test read task is red and dotted.  Second, the green path from our copy task is also dotted.  This is how SSIS signifies that these are OR operations.  Note that the constraint setting belongs to the target task, not to the paths, themselves. 

We need to do one more thing to keep SSIS from stopping the package execution when this task fails.  Right-click on the task and select properties.  We want to change the “FailPackageOnFailure” field from True (default) to false.  If it’s set to true, processing stops when the task fails.  We do, however, want to make sure the “failParentOnFailure” is set to true.

So now we’re handling a source read failure more gracefully.  What about a failure on the destination, though?  We probably ought to handle those, as well, right?  So we’ll do the same process with the truncate task.  When we’ve done that, our task looks like this:

So far so good.  What about that copy task, though?  It’d sure be nice to handle failures on that one.  We can’t, though, have two paths going between those last two steps.  Happily, we have more than just success and failure as options.  Double-click on the arrow between the last two tasks, and change the constraint option from “success” to “completion.”

Now, that arrow is colored blue, to indicate that it will proceed to the next step regardless of whether the previous step succeeded or failed.

And now, without much work or time, we have completed a project that would take a lot of work to do with traditional server scripting tools, and we’ve done it all within a single SSIS project.