Wouldn't You Want to Know Your SSIS Job Failed and Why? I think you would? Here is how.
I am presenting a useful feature of SSIS, Event Handlers. Event handlers can be used to capture events during the execution of a package. This is especially useful if you are trying to identify the root cause of an error. The message you will receive will be much more useful than the general error message you are accustomed to. You can transmit the output in an email as an alert, or you can log the event into a file or a table. The decision is yours. I will be demonstrating how to set up and event handler in a package and transmit the output via email. It is a very simple process and will save you time in identifying where the process is failing. I have seen the benefits of using Event Handlers pretty routinely capturing issues such as inability to connect to SFTP servers, files missing when from staging locations, inability to connect to databases, inability to open directories at run time, network bandwidth issues, the list goes on and on. As a bonus, the alert can be configured so that you will know what package and task elicited the failure. This is especially useful if you have a rather large ETL, involving many sub packages called in a specified sequence by a master package. Ideally, you would configure the Error Handler on the master package and any failures within the master package or sub packages will result in a detailed error message.
We will be creating a Event Handler that will capture errors when the package is executed. We will be using a File System task to perform an action that we know will fill to see the output from the expression task. We will configure the file system task to copy the file C:\Staging\myFile.txt and save it as C:\Staging\FileCopy.txt. The file does not exist and the task is basic, but it will fail and provide us with output from the Event Handler.
Begin by creating a majority of the variables that will be needed for use, by the Event Handler.
Name Data Type Value
vFromEmailAddress String Enter the email address that should appear as the originator of the email.
vProjectEnvironment String The environment the package is running in. Useful for identifying which environment the error is occurring in. During Development and Testing, I enter a value of "Development" or "Test". I enter the value of Production when the package/project has been tested and I migrate it to Production. This allows me to send all alerts to a centralized email group and we can focus on Production level issues.
vSubject String The subject of the email. We will use SSIS Error Alert in our example.
vSupportEmailAddress String Email address that can be used for contacting IT support. This email will be embedded in the email transmission. Transmissions are generally set up for transmission to my team. Some clients also ask that we include their email groups in the transmissions. We can embed the appropriate contact email address if it differs from the email address that initiates the transmission.
vToEmailAddress String Values of all of the addresses that should receive the transmission. Separate multiple email address values with a semi colon (;).
vFullFilePath String Full path to the target of our File System Task. For example, C:\Staging\myFile.txt
vCopyPath String Full path for renaming the file with our File System Task, i.e. C:\Staging\FileCopy.txt.
1.Drag a File System Task onto the SSIS canvas and name it appropriately.
2. Click on the canvas and then click on the Event Handlers tab in the upper portion of the window.
3. Click on the link that states "Click her to create an 'OnError' even handler for the executable 'Package'.
4. Drag a Send Mail task onto the canvas.
5. Double click on the Send Mail task to open the Send Mail Task Editor.
6. Name the task appropriately.
7.Click on "Mail" in the left hand portion of the window to configure two settings.
Configure your SMTP settings first.
Change the priority for the message to "High".
8.Then click "Expressions" in the left hand portion of the window. We will be populating the values for our Send Mail Task using expressions.
Click on the ellipses to the right of the expression. This will open up the Property Expressions Editor.
Select the property to configure.
Click on the ellipses to complete the configuration.
Click on the "OK" button when you have provided a value and evaluated the expression. Repeat the process for configuring the "Subject" and "ToLine" properties.
Next, select the MessageSource property and follow the prior steps to open the expression builder for the MessageSource property. You can build the following expression or simply copy and past the following expression.
" The Package "+ @[System::PackageName] + " has failed in "+ @[User::vProjectEnvironment] +" with the error(s) listed below"+"\n\r"+"
Error Code: "+ (DT_WSTR,50)@[System::ErrorCode]+"\n\r"+"
Error Description: "+ @[System::ErrorDescription]+"\n\r"+"
Error Source: "+ @[System::SourceName]+"
Please contact the support team "+ @[User::vSupportEmailAddress] +" to address any of your questions or concerns. We apologize for any inconvenience.
"+"\n\r"+"Thank you
"+"\n\r"+"Support Team"
Concatenating "\n\r" within the expression will provide a new line and carriage return at the specified locations. Once you have built the expression or copied and updated it according to your needs, evaluate the expression and click on the "OK" button. Your results should resemble the image below.
Click on the "OK" button then click on the "Control Flow" tab to return to our File System Task.
9.Double click on the File System Task we created and configure the task so that it resembles the image below.
Click on the "OK" button once configuration is complete.
10. Execute the package.
11. The package failed as we expected and transmitted an email to the specified recipients.
We have successful created an Event Handler to capture run time errors and transmit an email alert with detailed information. Here is some more information on the Send Mail Task or transmitting email using your personal email account with SSIS.
I hope you find this post useful. See you soon.