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:
- Create an Event Handler for
OnError
for the package level (Excutable is set to the package name). - Configured the SMTP connection and added in an Send Mail Task
- Within that task I configured an expression for the
Subject
andMessageSource
. 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:
- Task failed as expected, but the event handler failed as well.
OnError
event fired, except not as expected. First issue is truncation, received errorA 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:
- Second error for the same OnError:
property "MessageSource" cannot be evaluated. Modify the expression to be valid.
- I received two emails when the task failed.
Environment Info: SQL Server 2008 R2, running the package through BIDS only at this time.
Questions:
- 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? - 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?