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

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