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).

31 comments:

  1. Very nice!!!

    Thanks

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

    ReplyDelete
  3. 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. 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
  4. Paul, did you get the solution for your error?

    Thanks

    ReplyDelete
  5. 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. Yes it is in the Control Flow. Double-click the precedence constraint (green arrow) and set the constraint option value to "Failure."

      Delete
    2. 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. 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. I tried leaving it blank and making that change to the Propagate variable. It works! Thanks so much - this documentation is very helpful!

      Delete
  6. 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. Yes. I have the same issue as well. Setting the Propagate to False doesnt seem to work.

      Delete
    2. Is the data flow task in a sequence container?

      Delete
  7. Thank you. This helped a lot.

    ReplyDelete
  8. 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
  9. So if your loop fails 100 times you send 100 emails?

    ReplyDelete
  10. 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
  11. 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
  12. 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
  13. 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. 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
  14. 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
  15. Also, have you tried setting Delay Validation to false on the Excel source?

    ReplyDelete
    Replies
    1. 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. 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
  16. Set Delay Validation = TRUE (not false - sorry) on the Excel connection manager.

    ReplyDelete
    Replies
    1. 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
  17. Can you send me an email with the error that you're getting? david.elish@informationcollaboration.com

    ReplyDelete
  18. 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
  19. 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