Let SSIS notify you of it's own errors

One of the things I love best about SQL Server Integration Services (SSIS) is how powerful expressions are. In this blog I will talk about how you can enable SSIS to email you when a step has gone wrong. Pretty normal stuff you might think I could do that in DTS in truth yes you could but this is way better. The really cool thing is that we can actually include the error code and the error message in the email using expressions with very little coding.

For the purpose of keeping this entry concise I will make two assumptions the first is that the reader has an evironment capable of running the “Send Mail Task”. The second is that you have at least some familiarity with expressions. If you don’t it’s not a problem, just take a quick look at Andy Leonard’ s SSIS Expression tutorial, although it is so simple that I doubt you will need to!

The first thing we need to do is ensure that the package is going to fail. The simplest way to do this is to drag a task onto the control flow. In this package I have chosen to use a Data Flow task for the simple reason that it doesn’t require any mandatory configuration to make it run successfully. To make the task fail go to the properties of the task  and  change the value of ForceExecutionResult to Failure as shown in figure 1.

To make sure that we are emailed when this task fails rather than use the failure constraint we are going to make use of the OnError event. Click on your Data Flow Task to change the scope then click on the “Event Handlers” tab of your package. Click on the main area to enable use of the event and then drag on a Send mail Task. We will call this task “Data Flow Task”  Failure Notification as in figure 2.

Figure 2 - Adding email notification.

Set up the Send Mail task as you normally would and then we will enter some expressions to let SSIS notifiy us of the errors utilising some of SSIS’s system variables.
Double click on the send mail task, then click on “Expressions” in the Send Mail Task Editor window. For some reason the elipsis button doesn’t appear straight away so you have to click on the “Expressions” text box for it to appear, when it does click on it. This will open up a new window entitled “Property Expression Editor” which is where we will be setting our expressions. Click on the blank cell underneath “Property” and a drop down list will appear, the two properties that we wish to create expressions for are Subject and MessageSource.

For the Subject expression enter: @[System::PackageName] + " – "+  (DT_STR, 5, 1252) @[System::VersionMajor] +"."+  (DT_STR, 5, 1252)@[System::VersionMinor] +"."+ (DT_STR, 5, 1252)@[System::VersionBuild]  +": " + @[System::TaskName]

Click on the evaluate expression button and it should look something like:
Package1 – 1.0.1: “Data Flow Task” Failure Notification

Figure 3 - Entering expressions for the Subject property

Next we will enter the body of the email by setting the MessageSource property of the Send Mail Task:
"The package " +"\""+  @[System::PackageName] +"\"" + " failed with the errorcode: "  + (DT_STR, 100, 1252) @[System::ErrorCode] +"\n" +  @[System::ErrorDescription] +"\n\nThe error handling was triggered at: " + (DT_STR, 100, 1252)  @[System::EventHandlerStartTime] + "\n\nThe instanceGUID is " + (DT_STR, 100, 1252) @[System::ExecutionInstanceGUID] +"\n\nRegards,\n" +  @[System::CreatorName]

Click on the evaluate expression button and it should look something like:

The package “Package1″ failed with the errorcode: 0
The error handling was triggered at: 08/11/2009 17:04:12
The instanceGUID is {EAC1D5C7-7250-45B1-AE32-4F32F2E3B9B6}

Regards,
Richard Douglas

Figure 4 - Entering expressions for the MessageSource property.

Once this is done execute your package and within a few moments you will receive your own self documenting error messages by email. This could make things extremely helpful if you are on the move and your phone can accept emails you can get straight to the heart of the issue.