Handling Multiple Errors in SSIS Revisited

Previously, I posted about how multiple errors in SSIS can be combined into a single unit for emailing or logging. Recently, a thread on the MSDN forums pointed out that the example wasn’t quite complete. While it does show how to collect all the error messages and get them into a single string, it doesn’t show how to email or log that string only once. So I’m giving it another shot, and show two options for handling this. This post does build on the previous one, so please read it before proceeding.


If you download the sample package in the previous post and examine it, you’ll see that there is a script task in the OnPostExecute event. This script task is where the error messages are assembled, and the code to deliver the bundled error message would be placed. Please note – the script does not actually email or log the message, that was left open for the reader to implement.


If you run the package, you’ll notice that the OnPostExecute event fires twice. It’s firing once for the data flow, and once for the package. Since the OnPostExecute event handler is where the script is located, this causes the message to be delivered twice – not what we want. To work around this, we need to make sure the OnPostExecute only fires once. One way to handle this is to set the Propagate system variable to false. The Propagate variable controls whether the event is raised to the next container. In this case, if Propagate is set to false on the data flow’s OnPostExecute event, the package’s OnPostExecute will only be fired once.


image


However, this approach requires that all tasks in the package need to have the Propagate variable set to false in their OnPostExecute event handlers. To work around this, I recommend incorporating a single Sequence Container, with the Propagate variable set to false on it’s OnPostExecute. Any tasks in the package should be added inside the Sequence Container. If you do this, the Propagate variable only needs to be set once.


image


Another approach is to eliminate the use of the OnPostExecute event altogether. To do this, the Script Task needs to be moved to the control flow, and the OnPostExecute event handler can be deleted altogether. A Sequence Container should still be used to hold all of the tasks that need to be grouped for error handling. The Script Task should be connected to the Sequence Container with a Failure constraint.


image


This approach also ensures that the Script Task will only execute once, if there is a failure in the Sequence Container.


Hopefully, this helps clarify how to leverage collecting the error messages to only send a single error result. I’ve uploaded the samples here, so please feel free to take a look at them and let me know if you have questions.

15 Comments

  1. http:// says:

    Thanks for the great tip. This worked great, but still sends an email even if the package completes successfully. To work around this, I did the following:

    In Event Handlers, on the OnPostExecute event handler after the script task I created an send mail task. On the Precedence Constraint Editor I selected Expression and Constraint for the evaluation operation. For the expression, I put LEN(@emailText) > 10

    This will help prevent emails being sent for every successful run. Again, thanks for the great suggestion.

    Joseph

  2. http:// says:

    Thanks for this handy article. I’ve implemented it using the OnPostExecute event, and it works great. One thing I’m trying to figure out is how to allow certains tasks to continue after the error description has been saved to the error collection. Currently, when an error occurs, the errors are assembled and e-mailed, but the package stops execution. I’ve set MaximumErrorCount of the failing task to 0 (I think this makes it so that any number of errors may occur), and then while the package can continue on from that point, the parent container still fails and the package halts execution. I could set the MaximumErrorCount of all ancestors to 0, but that seems a bit messy.

    If this is the wrong place to post this question, hopefully you can point me in the right direction?

    Much thanks.
    Kenny

  3. jwelch says:

    The best place to ask these types of questions are the MSDN Integration Services forums, at http://forums.microsoft.com/Forums/ShowForum.aspx?ForumID=80&SiteID=1.

    On this particular issue, you may need to either set the Propagate variable to FALSE in the error handler, make sure FailParentOnFailure is set to FALSE, or, as a last resort, set the Max Errors on the containing objects to 0 or a large number.

  4. http:// says:

    Thanks for the quick response. I’ve posted a topic here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3093944&SiteID=1.

    I would prefer to not mess with the error handler. What I liked about your article was that the error handling was just in the one place (at the package level), so I in fact do want the error event to propagate all the way up. As for FailParentOnFailure, that is set to False as is FailPackageOnFailure.

  5. http:// says:

    … sorry about that. Wasn’t sure if URLs would automatically render as links. The link is http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3093944&SiteID=1.

  6. http:// says:

    First of all, thanks for this article and the previous one, they both been of great help.
    Instead of using the Propagate variable or the Sequence Container, I’ve come up with a solution using a user boolean variable ERROR_EMAIL_SENT that is included in the Precedence Constraint that leads to Send Mail Task, suggested above by boschertjd18. The expression becomes :

    @[User::ERROR_EMAIL_TXT] != “” && !@[User::ERROR_EMAIL_SENT]

    This variable is created as False and set to True in a Script Task after de email has been sent.

    Thanks
    Rui Santos

  7. http:// says:

    I like this post very much. I have a question though.
    When I use this method to send the custom error log through send mail task, I assign the mail content to a varaible. So when there is an errorr in the package, the mail displays the error log, but when the package succeeds, I get an empty mail without any content. CAn somebody suggest how I should be able to display the message ” The package succeeded” in the mail content when the package succeeds instead of sending a blank message.

    Appreciate your response
    Thanks

  8. jwelch says:

    You can add an expression to the constraint leading to the Send Mail task, and check to see if the string is empty or not. Just right-click on the constraint, and choose Edit.

    If the Send Mail task has no constraints, you may need to add an empty task (a Script Task works well) and create a constraint from the Script Task to the Send Mail task.

  9. Armin says:

    Hi!

    Images do not show. Can this be fixed, please. Thank you very much!

    Armin

  10. Frisco says:

    This article is excellent, Helps a lot. Thank you.

  11. Hi John,

    implemented it today in my project at the customer side! Works great, simpe and easy but effective.

    Dietmar

  12. Albert says:

    Instead of tricking, I just empty the errorMessages variable after the mail is sent like this:

    Dts.Variables(“User::errorMessages”).Value = Nothing

  13. Hui SHi says:

    hi John, excellent article. However, if use the second approach which is to add a send mail task after a huge sequence container, it is not possible to pull out the values of ErrorCode, ErrorDescription which only exist in the scope of OnError

    • jwelch says:

      That’s true. In that case, you might want to combine techniques – use a script task in the OnError event of the container that collects all messages into a single variable (append to the value, don’t overwrite) and then send that in the final step after the container completes.

Leave a Reply