Featured Database Articles

Setting Up Email Notification for SSIS Package Failure

As a DBA, we often setup monitoring to receive job failure notification, but when it comes to SSIS packages, we either do not capture the job failure (if the job runs through the command prompt) or we have no idea why it failed. In this article, I'd like to walk you through how to enable the logging functionality for SQL Server Integration Services (SSIS) and how to capture detailed information for immediate troubleshooting without "re-run" the package.

This article is written and tested for SQL Server 2005 and 2008.

Enable SSIS Logging

To capture detailed information on why an SSIS package failed in execution, we first need to enable logging functionality. Follow the steps below to enable Logging on a package.

Step 0: Select the package that you will be working on and make a copy of the package in case you want to rollback the change.

Step 3: Select Package Location as File System. (If you store the package within SQL Server or SSIS Package store, then export the package first, follow the steps then import it back to SQL Server or SSIS Package Store)

Add copy of existing package: File System

Load Package

Add copy of existing package: Package path

Step4: Double click on [MySSISPackage] to open the package

Open the package

Step 5: Define a "Connection Managers" to store logging information

Define a Connection Managers

In this article, I will be storing logging information in SQLConnection. Double click on the designated "Connection Manager" (in this case, it will be SQLConnection); it should be pointed to the right SQL instance name and database name where SSIS logging information will be recorded. Here, I will be storing the logging information in SQL instance MSSQLENG\TEST1 and the database is SSIS_Config.

Click Test Connection to make sure you have connectivity to the database.

Click Test Connection

Step 6: Right click on the white pane

Right click on the white pane

Step 7: Select Loggings

Configure SSIS Logs: MySSISPackage

Step 8: Select "SSIS log provider for SQL Server"

Configure a new log

Step 9: Then click Add

Add the new log

Step 10: Make sure these two check boxes are selected.

Check boxes

Step 11: Select the Connection Managers name where you want the logging information to be recorded. In this example, I will be storing SSIS loggings in SQLConnection. Please find SQLConnection connection configuration in Step 4.

Select the Connection Managers name

Step 12: Click Detail Tab.

Details tab

Step 13: Select these three events for error loggings.

OnError

OnPostExecute

OnTaskFailed

Select events for error logging

Click OK.

Step 14: Make sure the package is saved back to original package location.

Save the package back to it's original location

Then click Save.

Confirm Save As

You will be asked if you want to overwrite the package. Click Yes.

Now we have successfully enable loggings on the package.

Step 15: Now let's test and make sure SSIS execution log is recorded.

Make sure SSIS execution log is recorded

Click F5 to execute the package manually

Execute the package

Step 16: Check SSIS logging info

Here, I use SQL Server Management Studio (SSMS) to connect to [MSSQLENG\TEST1], database [SSIS_Config], and then issue the query below to review the execution log of the manual run I just triggered.

I can see that the error message is clearly recorded and that it provides detailed information on why the package failed on execution.

The error message with detailed information

The message I got here is:

Executing the query "execute test" failed with the following error: "Could not find stored procedure 'test'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Up to this point, we have confirmed that the SSIS log is functional. Next, let's create a stored procedure that queries SSIS logging info and sends out email when an error is captured in the log.

Send Email When SSIS Failed in Execution

Let's continue to use SSIS_Config database to create a stored procedure. The stored procedure will allow you to pass the @ToEmail, @CCEmail and @minute parameters. You can define the email address for the designated To and Copy recipients. The @minute parameter will allow you to define how many minutes you want to trace back for any SSIS failure. If you pass 180 for @minute, you will be notified for any SSIS package execution failure up to 3 hours back from the current time.

Execute the query below on the database that Connection Manager "SQLConnection" connects to.

To test the process, execute the stored procedure with @ToEmail and/or @CCEmail and @minute. The example below sends SSIS failure notification to claire.hsu@test123.com, with failure occurrences for the past 30 minutes.

Say you have two SSIS packages that failed within the past 30 minutes; you will receive two emails similar to the picture above. (I have masked information for company privacy). The email will contain the following information.

Subject: SSIS Package [PackageName] failed on [SQLServer Name where you store the logging info]

You can set a job that runs the stored procedure every 30 minutes. It will send out email only if a package failed within the last 30 minutes. For every package failure, you will receive one email.

Conclusion

By enabling SSIS logging on the package and setting up automated process to query logging information, we no longer need to do any guess work on what went wrong for the package; we can also skip the pain of reproducing the error and can be notified within a reasonable timeframe when any package fails on execution.