Continuing a Loop After an Error

A common question on the SSIS forums is how to continue a loop after an error occurs. For example, you might have a For Each Loop container that iterates through a set of files in a folder. If one file is invalid, you want to continue processing the remaining files. Fortunately, there is a simple pattern that allows this to be accomplished.

I created a package to illustrate this. Basically, the package implements the scenario above. It consists of a For Each Loop to iterate through all the files in a folder. There is a data flow task that processes each input file, and runs it through a row count (just for illustration purposes). The Script task is there to illustrate an error handling task could be attached to the data flow, but it’s empty in the example package. It would need to be replaced with the appropriate task to handle a failure, such as a File System task to move the file to another folder.

image

The key to handling the errors is the Sequence container, or more specifically, how the Sequence container is set up. The OnError event handler for the Sequence container has been enabled. You can do this by selecting the Sequence container, then clicking the Event Handlers tab. Select the OnError event in the drop down, then click the link in the designer to create an empty event handler. It’s not necessary to add anything to event handler.

image

Once it has been created, go to the Variables window, and select the option to show System variables. Select the Propagate variable, and set the value to FALSE. This prevents any errors occurring on tasks inside the Sequence container from being raised to the For Each loop.

image

This is all that really needs to be done, but if you run the package with only this set, you’ll notice that the Data Flow task and the Sequence container both report an error. To prevent the Sequence Container from reporting an error, set the MaximumErrorCount property of the container to 0.

image

Note that you do not have to use a sequence container to accomplish this. You could make the same settings on individual tasks inside the For Each loop. However, the Sequence container gives you a common place to make the settings, and you can have as many tasks inside it as you’d like.

The example package is posted on my Skydrive. The zip file contains a folder call Import. The package expects this folder to be located in C:\Temp. It contains 4 text files for the package to process. The 2nd file contains an error (one of the rows has fewer columns than it is supposed to), so that you can see the error behavior.

19 Comments

  1. http:// says:

    Thanks for the nice explaination jwelch. Its really helpful!!

  2. Tim says:

    I found that I had to set the “Sequence Container” MaximumErrorCount to zero aswell as the “Database Loop Container” in order to allow the outer loop to keep processing after an error occured. Thanks for pointing me in the right direction.

  3. Andy May says:

    I had the same problem as Tim, but I was using 2008 R2, don’t know if that made a difference

  4. Ned says:

    This is awesome!

    Thanks

    Ned

  5. malar says:

    hi this is not working for me. could u pls guide me in right path. its urgent

    • jwelch says:

      I need a little more information on what’s not working to be able to help you.

      If it’s truly urgent, you should call Microsoft support. I can’t respond to blog comments immediately, unfortunately.

  6. mmckee says:

    Great solution, but I keep getting the popup window when the process errors, which means I have to acknowledge the popup window before it will continue. Any idea how to suppress it?

    • jwelch says:

      I’m not quite sure what you are referring to. The sample code I provided doesn’t display any popup windows. Are you using an Execute Process task to run an executable? If so, you may need to check whether the executable supports a “silent” mode.

  7. Eddy Jawed says:

    There’s another guy called Rushabh Mehta who tried to explain the same thing. However his explanation is nowhere near as good as yours, THANK YOU SO MUCH!

  8. Pete says:

    Thanks – this is exactly what I was after. I’d previously tried wrapping everything in a script task as a workaround but Info events weren’t being captured further up by my handlers. Implenting your suggestion allowed me to use the Execute Package task as normal, and everything works perfectly now.

  9. Naresh Jagtiani says:

    Thanks, this helped me. Basically I had a for each loop container and a send mail task inside it. If the first email address was wrong, it would fail the job. By setting maximumerrorcount = 0, the job continued to run after the bad email address.

    Thanks again

  10. Dgyp says:

    OMG! Yes, this was the solution I’ve been searching around for. The final trick for me was setting MaximumErrorCount = 0 in my task that is failing, but I don’t want the package to fail (which “Propigate” doesn’t seem to do on it’s own).

    I’ve read a lot of solutions, I think each SSIS package is so unique that what works for one person doesn’t always work for another. I think a lot of the solutions I read just allowed the package to continue running – but in our environment we’ve got job schedulers that are capturing the return-code from the package. In most of the solutions, the return code was NOT (0), it was (1).

    THIS solution allows the package itself to not fail AND allow the tasks to continue (and all other tasks to behave normally).

    thanks a LOT!

  11. Nathan Holmes says:

    Nice clear ‘how to’ article, but I don’t understand why it’s important to wrap the failing task (Data Flow) and the tasks on the failure path (Script Task) into a Sequence Container. What’s wrong with placing the Data Flow Task and Script Task directly within the Foreach Loop, dropping the Sequence Container completely, and setting Propagate:OnError = False and MaximumErrorCount = 0 on the Data Flow Task itself?

    I’ve spent most of today banging my head against an implementation without a Sequence Container that isn’t working so, having read this, I’m going to try adding one tomorrow… but I really want to know what it’s bringing to the party.

    • jwelch says:

      I believe when I tried it without the Sequence Container, failures immediately caused the For Each Loop to fail processing. But honestly, it was a while back – I’d need to go back and check again.

  12. szuliman says:

    Thanks for this article, this works great.

  13. Maria says:

    Works fine for one server, but fails on another (same package). How can it be? Are there any settings for server or something like that?

  14. John Welch saves the day for me again! Thanks John!

  15. Ben says:

    This worked. Thank you!!!

Leave a Reply