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.
Make a copy of the SSIS package
Step 1: Open Integration Services Project
Visual Studio 2008 Solution Explorer
Step 2: Right click on SSIS Packages --> select "Add Existing Package"
Add Existing Package
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
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.
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.
Step 13: Select these three events for error loggings.
Select events for error logging
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.
Review the execution log
Step 17: Check SSIS logging info for failed execution
I would also be interested to see what error message is captured if the package failed on execution. Here I intentionally fail the package execution.
Failed package execution
Now I run the same query but focus on the rows for my failed run.
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:
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 email@example.com, with failure occurrences for the past 30 minutes.
Stored procedure sends email
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]
- SQL instance: [SQLServer Name where you store the logging info]
- Package Name: [PackageName]
- Job Originating Host: [From which machine the package was executed]
- RunAs: [Which login ran the package]
- Start DT: [Package start time]
- End DT: [Package End Time]
- Error Message: [Detail information on why the package failed on execution]
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.
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.