Sql-server – SSIS Event Handler at Package Level

sql-server-2008-r2ssisssis-expressions

I have a fairly simple SSIS package (handful of Execute Task and a few Data Flow task). The end result I am looking to have:

  • I would like to get notified by email when an error occurs anywhere
    in the package
  • I would like that email to include: Package name, failed task, and error information.

As I understand I set this up in the following manner:

  1. Create an Event Handler for OnError for the package level (Excutable is set to the package name).
  2. Configured the SMTP connection and added in an Send Mail Task
  3. Within that task I configured an expression for the Subject and MessageSource. Those two expressions are noted below.

Expressions:

Subject – "Error occurred on " + @[System::PackageName]

MessageSource – (line breaks added for readability)


(DT_WSTR,250)("An error occurred in execution of the package: " +  @[System::PackageName] + "\n" + 
"The task that experienced the error: " +  @[System::TaskName] + "\n" + 
"The error information returned:\n" + 
"Error Code: " +  (DT_WSTR,50)(@[System::ErrorCode]) + "\n" + 
"Error Description: " +  @[System::ErrorDescription])

Now what occurred after I forced an error:

  1. Task failed as expected, but the event handler failed as well.
  2. OnError event fired, except not as expected. First issue is truncation, received error A truncation occurred during evaluation of the expression. This is the content of the email received, which I don't see the expected new lines:

An error occurred in execution of the package:  The task that experienced the error: Failure notification The error information returned:
Error Code: 0
Error Description:
  1. Second error for the same OnError: property "MessageSource" cannot be evaluated. Modify the expression to be valid.
  2. I received two emails when the task failed.

Environment Info: SQL Server 2008 R2, running the package through BIDS only at this time.

Questions:

  1. What do I have wrong in the expression for the MessageSource? I can obviously evaluate the expression successfully in the properties window, and understand that is because the variables are not populated. So how do you configure that were it will not truncate no matter what error is passed in, just set it to some large number length?
  2. I am not looking for some custom method or code to perform this as the package is fairly simple and is only run every blue moon. However, would it be best to simply create an OnError event for each task? As I understand it any child task that fails has the error information passed up to the parent package, so it should work right?

Best Answer

Pre 2012, expressions were limited to 4k. That's not a Variable's limit, you can assign stupid-long values to a Variable, it just can't be done with expressions. Use a Script Task to do the concatenation and assignment to your variable and see if that doesn't clear up #1

The thing you're likely to run into, and that I never found a satisfactory resolution, was the double firing of messages. I know, they say set propagate to false and all of that but I never seemed to get it to work as I wanted. In our world, a package would fail if an error raised so the OnTaskFailed event was more useful for only getting the signal once but your mileage my vary.

A better approach we used at a different job was to have a process sweep the sys.ssiserrorlog table every N minutes looking for failures. Since all packages logged to it, it was a cleaner and more reusable approach for us.

I've seen people make mention of always running packages from the context of a parent package to adhere to the DRY (don't repeat yourself) principle so the parent has the error notification and such built into it.

The final bit to be aware of, don't rely on SSIS's error handling exclusively. We had a deploy that went well, or so we thought. When the package fired though, it failed to validate as the metadata for a table didn't match what the data flow expected. A package failing to validate is like a syntax error and so the OnError/OnTaskFailed event's didn't even get a chance to fire and tell us that we were in an awful state. And we went a few days without noticing. Oopsie

Related Question