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.

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

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.

use SSIS_Config

go

select id, event, source, starttime, endtime, message from sysssislog 

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.

use SSIS_Config
go
select id, event, source, starttime, endtime, message from sysssislog

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.

Connection Manager

USE SSIS_Config
go
 
CREATE PROCEDURE usp_FailedSSIS_SendMail
          @ToEmail varchar(1000) = '',
          @CCEmail varchar(1000) = '',
          @minute int = null 
AS
SET NOCOUNT ON 
declare c4 cursor for 
select  id , event , computer , operator , source , sourceid , 
	executionid , starttime , endtime , message  
from sysssislog where executionid 
in (select executionid from sysssislog where event = 'OnError' )
and starttime > dateadd(mi, -@minute, getdate()) order by executionid, id 
 
open c4
declare @id int, @event varchar(256), @computer varchar(256), 
	@operator varchar(256), @source varchar(256), @sourceid uniqueidentifier, 
	@executionid uniqueidentifier, @starttime datetime, @endtime datetime, 
	@message varchar(1024), @errormsg varchar(4000)
 
declare @startid int, @cur_package varchar(256), @endid int, @pre_id int, 
	@start_time datetime, @end_time datetime, @cmd varchar(8000)
declare @subject1 varchar(256)
 
set @errormsg = ''
set @cmd = ''
fetch next from c4 into @id , @event , @computer , @operator , @source , 
	@sourceid , @executionid , @starttime , @endtime , @message 
while @@fetch_status = 0
begin
 
          
          if @message like 'End of package execution.%'
          begin 
                   set @endid = @id
                   set @end_time = @endtime
                   
                   SELECT @startid = id from sysssislog where executionid = @executionid and 
						event = 'PackageStart' and message like 'Beginning of package execution.%'
                   SELECT @start_time = starttime from sysssislog where executionid = @executionid and 
						event = 'PackageStart' and message like 'Beginning of package execution.%'
                   select @errormsg = @errormsg + message from sysssislog where id between @startid and 
						@endid and executionid = @executionid
                   
                   set @subject1 = 'SSIS Package ' + @source + ' Failed on ' + @@SERVERNAME
                   
                   select @cmd = @cmd + 'SQL Instance: ' + @@SERVERNAME + char(10)
                   select @cmd = @cmd + 'Package Name: ' + @source + char(10)
                   select @cmd = @cmd + 'Job Originating Host: ' + @computer + char(10)
                   select @cmd = @cmd + 'Run As: ' + @operator + char(10)
                   select @cmd = @cmd + 'Start DT: ' + convert(varchar(30),@start_time,121) + char(10)
                   select @cmd = @cmd + 'End DT: ' + convert(varchar(30),@end_time,121) + char(10)
                   select @cmd = @cmd + 'Error Message: '+ char(10) + @errormsg 
                   
                             exec  msdb.dbo.sp_send_dbmail 
                             @recipients= @ToEmail,
                             @copy_recipients = @CCEmail,
                             @subject =  @subject1, 
                             @body_format ='TEXT',
                             @body = @cmd
                   
          set @errormsg = ''             
          set @cmd = ''
          end
set @pre_id = @id
fetch next from c4 into @id , @event , @computer , @operator , @source , @sourceid , 
	@executionid , @starttime , @endtime , @message 
end
 
close c4
deallocate c4
GO

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.

exec usp_FailedSSIS_SendMail @ToEmail='claire.hsu@test123.com',@minute = 30

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.

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.

See all articles by Claire Hsu