Wednesday, December 29, 2010

SSIS Foreach Loop Container: Continue on Error

I recently had a project that involved processing FTP log files. I chose to use SSIS for the task as it is great for this type of flat file ETL work. One of my requirements was to continue loading remaining log files even if an unhandled exception was encountered during the processes.

What I found is that the default behavior of the Foreach Loop container is such that any unhandled exceptions within the container cause it to exit the loop. Not what I wanted.

What I did want was for the container to do something on error (move the file to an error folder and send a notification email) and continue on to the next file. You can see this in the screen shot of the container below. Note the failure precedence constraint on the right hand side (red arrow).

Just like a C# application, unhandled exceptions "bubble-up" to their parent and if none of the objects handle the exception the program fails. In this case I wanted to know of the error, fail the child executable (in this case it was a Data Flow) but continue executing the parent (Foreach Loop Container).

It turns out this is pretty easy to do. The first thing you need to do is create an OnError event handler for the Data Flow (or any other child executable). Once the event handler is created show the system variables in the variables window, locate the Propagate variable and set it to false.
The Foreach Loop Container is now aware that the Data Flow has failed but the exception is handled in the child and therefore does not cause the container to fail and exit. We can now use the failure Precendence Constraint to do our additional processing (move the file to an error folder and send a notification email).
at

31 comments:

  1. AnonymousMarch 21, 2011 at 4:31 AM

    Thanks Man

    ReplyDelete
  2. AnonymousApril 15, 2011 at 12:21 PM

    Very nice!!!

    Thanks

    ReplyDelete
  3. UnknownMay 26, 2011 at 8:02 AM

    Things like this don´t show up easily on the documentation, thanks a lot !!

    ReplyDelete
  4. PaulJuly 7, 2011 at 1:41 PM

    Hi, Need help in importing data to SQL Server by SSIS.

    I hve 4 excel files and intentionally put error in 3rd file(by changing the header name). I want to import the data to SQL Server by SSIS(importing 1, 2 and 4th file) ignoring the 3rd file.

    Control used to do that : For Each Loop, Sequence Container, Data Flow task

    Tried by propagate = false for control Sequnce Container.. but the error bubbles to For each Loop and stops there.

    Not able to load the data :(

    Any help is highly appreciated

    Thanks
    Paul

    ReplyDelete
    Replies
    1. d.elishJune 4, 2012 at 10:53 AM

      I think you might need to set it on the sequence container as well as the child control flow tasks. Have you tried this?

      Delete
  5. AnonymousAugust 31, 2011 at 12:35 PM

    Paul, did you get the solution for your error?

    Thanks

    ReplyDelete
  6. AnonymousJune 25, 2012 at 2:54 PM

    Is this ForEach loop in the Control flow? I do not have the red error output available from the Data Flow task on the Control Flow tab. Is there some setting I need to change?

    ReplyDelete
    Replies
    1. d.elishJune 25, 2012 at 3:06 PM

      Yes it is in the Control Flow. Double-click the precedence constraint (green arrow) and set the constraint option value to "Failure."

      Delete
    2. AnonymousJune 25, 2012 at 3:26 PM

      Thanks. Can you show the setup for the OnError event handler for the Data Flow? I have it redirecting properly when there is a failure, but the package currently stops running. I'd like to see the details of how you setup the error handling.

      Delete
    3. d.elishJune 25, 2012 at 3:29 PM

      I posted this a while ago and don't know if I still have the source. I don't think there's anything specific about the event handler beyond setting the System::Propagate variable to false. Have you done this? If so, what tasks do you have in your Event Handler?

      Delete
    4. AnonymousJune 25, 2012 at 3:34 PM

      I tried leaving it blank and making that change to the Propagate variable. It works! Thanks so much - this documentation is very helpful!

      Delete
  7. AnonymousJuly 13, 2012 at 1:39 AM

    I tried setting the Propogate variable to False for the "Data Flow Task", but it still gives the error and stops the package.

    ReplyDelete
    Replies
    1. AnonymousNovember 16, 2012 at 3:21 PM

      Yes. I have the same issue as well. Setting the Propagate to False doesnt seem to work.

      Delete
    2. d.elishNovember 16, 2012 at 9:04 PM

      Is the data flow task in a sequence container?

      Delete
  8. Eric McWhorterFebruary 26, 2013 at 7:57 AM

    Thank you. This helped a lot.

    ReplyDelete
  9. nerd.February 27, 2013 at 3:58 AM

    Hi David,

    I have a similar issue trying to load Excel files which are (sadly) not always a consistent format.

    I want my loop to skip over the bad ones (and do something like move them to another folder) but my loop and package keep stopping at the first bad file.

    I have set Propagate=false on the OnError event handler for the Dataflow task and I have added a Sequence Container around the Dataflow task (inside the Foreach loop). The error is still 'bubbling up' and stopping execution of the whole package.

    Any ideas what I'm doing wrong?

    ReplyDelete
  10. AnonymousDecember 10, 2013 at 8:37 AM

    So if your loop fails 100 times you send 100 emails?

    ReplyDelete
  11. d.elishDecember 10, 2013 at 8:44 AM

    That would depend on your requirements. If there were 100 different files that failed and the users wanted to know which files had issues, then it might be appropriate to send 100 emails. If the requirement is that the users be notified that any one file within a set failed then you could implement a counter and a precedence constraint using that counter to only send one email.

    ReplyDelete
  12. AnonymousNovember 19, 2014 at 9:40 AM

    Hi, I got the same issue that nerd post here, but I am using flat files, and BuLk Insert, even with the propagate set to false, and the failure precedence it stop, and it doesn't continue with the next file in the ForEach Loop Container. Any Ideas?

    ReplyDelete
  13. Les KellyJune 21, 2016 at 7:25 AM

    Hello David,

    I know it's been a long time since there have been any active post to this blog but hopefully you see this and can give me a suggestion. I have a package created in Visual Studio 2008. In the package is a data flow task which has an excel source. The excel file is periodically downloaded from a third party. It usually contains a list of records but if there is no activity it will be empty. When empty the package throws an error and ends execution. The package is run by a SQL server job. What I want it to do in this instance is send an e-mail and complete execution.

    I placed the data flow inside of a Sequence Container and set the propagate variable for the Sequence Container and the data flow task to false and have a Send Mail task connected to the data flow by a Precedence Constraint with it's value set to Failure. The package sends the email but the SQL Job still fails. Any suggestions?

    ReplyDelete
  14. d.elishJune 21, 2016 at 7:47 AM

    Hi Les. Did you "...create an OnError event handler for the Data Flow (or any other child executable). Once the event handler is created show the system variables in the variables window, locate the Propagate variable and set it to false."

    ReplyDelete
    Replies
    1. Les KellyJune 21, 2016 at 8:54 AM

      Hello David,

      Thanks for your response. Yes I created the OnError event handler for both the Sequence Container and the Data Flow task and set the Propagate variables to false.

      Delete
  15. d.elishJune 21, 2016 at 8:57 AM

    Are you sure the error is the same error you were getting before? The one related to the empty Excel file. You're not getting a validation error are you?

    ReplyDelete
  16. d.elishJune 21, 2016 at 9:01 AM

    Also, have you tried setting Delay Validation to false on the Excel source?

    ReplyDelete
    Replies
    1. Les KellyJune 21, 2016 at 9:15 AM

      It is a validation error but I only get it when the Excel file is empty.
      I don't see a Delay Validation property for the Excel source. I do see it for the Data Flow Task that the data source is in but it is already set to false.

      Delete
    2. Les KellyJune 21, 2016 at 9:32 AM

      It is a validation error but I only get it when the Excel file is empty.
      I don't see a Delay Validation property for the Excel source. I do see it for the Data Flow Task that the data source is in but it is already set to false.

      Delete
  17. d.elishJune 21, 2016 at 9:40 AM

    Set Delay Validation = TRUE (not false - sorry) on the Excel connection manager.

    ReplyDelete
    Replies
    1. Les KellyJune 21, 2016 at 9:54 AM

      I set the Delay Validation to true on the connection manager for the Excel data source but the results are the same. Any other thoughts?

      Delete
  18. d.elishJune 21, 2016 at 9:59 AM

    Can you send me an email with the error that you're getting? david.elish@informationcollaboration.com

    ReplyDelete
  19. Julian KooikerMarch 6, 2017 at 8:19 AM

    For people that have the same issue that the package will still fail en the loop does not continue:
    As an addition to this tutorial you should also set the maximumErrorcount of the ForEachLoop to 0 and the FailParentOnFailure property of the forEach Loop to true.

    ReplyDelete
  20. Julian KooikerMarch 6, 2017 at 8:22 AM

    I had the same issue that the package failed while i followed the steps in this tutorial:
    as an addition to this tutorial you also have to set these properties at the 'ForEach Loop' object:
    1. FailParentOnFailure: true
    2. MaximumErrorCount: 0

    ReplyDelete