Bringing back an informative SQL Server Agent job email notification on failed SSIS packages

Introduction

The SSISDB database (a.k.a. the Integration Services catalog) was introduced back in SQL Server 2012 as a way to de-clutter MSDB database and provide some built-in logging and reporting infrastructure. In a nutshell, SSISDB is an SSIS framework (see Fig #1) making SQL Server Integration Services more robust and enterprise-friendly by providing the following features:

• Database backup
• Database encryption
• Support for environments
• Support project and package parameters
• Package versioning
• Custom SSRS performance reports built into SSMS
• Support for deployment within SSDT

While overall SSISDB Catalog was and still is a great enhancement to SQL Server Integration Services, some previously working fine feature took a wrong turn. This blog post is going to cover the “behavioral” changes that Microsoft made to SQL Server Agent job notification as part of the SSISDB Catalog, specifically the error message reporting on failed package.

Problem

Prior to SQL Server 2012, an email notification on a failed SQL Server Agent job step with an SSIS package was really informative. The error message was specific enough, as it was providing the error from the SSIS execution engine. When Microsoft introduced SQL Server 2012, it decided to replace this specific SQL Server Agent job email notification with a generic message directing the user to the SSIS Catalog error log (see Fig #2).

I’m assuming when most of us are receiving Operational emails, we want to know not only if to react or not, but also how to react. This is especially important now, since we use mobile devices to read emails and might not have access to the a real computer.

Unfortunately, the email above gives only an indication that some SSIS package failed without giving any details. For example, in the past (prior to SQL Server 2012), you would know that your package failed due to connectivity issue by just reading an email and decide to ignore the notification. Now you don’t have that option. You would need a real computer to fire-up SSMS and run a custom SSRS on that package, adding many clicks to see the details error. Needless to say, running to your desktop/laptop to open SSMS only to discover connectivity problems is unnecessary overkill.

Solution: Use T-SQL to create a better SQL Server Agent job email notification 

The solution will involve a T-SQL query (I should probably make it a stored procedure at some point) that would connect tables and records between both MSDB and SSISDB databases to bring back a detailed message on a failed SSIS package. Equipped with an email that has detailed error message, you can react without leaving your couch – fix it or just let it retry.

In a nutshell this solution is to going to:

1. Find the job name based on a current SPID
2. Find a job with a failed step
3. Connect step with SSIS package name
4. Use the SSIS package name to find run-time errors in SSISDB catalog
5. Bubble us to an inner exception to include only detailed error
6. Locate a default job operator
7. Send a detailed error report

The following script would need to be added as last step in a job (you can disable the job alert) with all the steps that include SSIS packages going to that last step on failure (Fig 3).

Additionally, the used-to-be last step before Report SSIS failure was introduced that will now precede that step will need to quit the job reporting success (Fig 4) instead of continuing to the next step..

Source code

DECLARE @Full_ssis_command VARCHAR(4000),
 @Job_step_id INT,
 @Package_name VARCHAR(4000),
 @EMailBody NVARCHAR(2000),
 @EMailSubject VARCHAR(150),
 --@Job_name VARCHAR(100),
 @Job_id UNIQUEIDENTIFIER
 
SELECT	@Job_id = Job_id
		--@Job_name = [name] --AS job_name
FROM	sys.dm_exec_sessions AS ions
	INNER join msdb.dbo.sysjobs AS jobs ON jobs.job_id
				= CAST(CONVERT(BINARY(16), SUBSTRING(ions.[program_name],CHARINDEX('(Job 0x',ions.[program_name],1) + 5 , 34),1) AS UNIQUEIDENTIFIER)
WHERE	1=1
	AND ions.session_id = @@SPID --63
 
--print @Job_name
 
SELECT TOP 1 @Job_step_id = Step_id
FROM msdb.dbo.sysjobhistory (NOLOCK)
WHERE Run_status <> 1
 AND Step_id > 0
 AND Job_id = @Job_id
ORDER BY Instance_id DESC
 
SELECT @Full_ssis_command = Command
FROM msdb.dbo.sysjobsteps (NOLOCK)
WHERE Job_id = @Job_id
 AND Step_id = @Job_step_id
 
 PRINT @Full_ssis_command
 
IF @Full_ssis_command LIKE '%.dtsx%'
 BEGIN
 SELECT @Package_name = RIGHT(LEFT(@Full_ssis_command,CHARINDEX('.dtsx', @Full_ssis_command)), CHARINDEX('\', Reverse(LEFT(@Full_ssis_command, Charindex('.dtsx', @Full_ssis_command)-1)))) + 'dtsx'
 END
 
SELECT TOP 1 @EMailBody = LEFT([Message], 2000), @EMailSubject = 'Package : ' + Package_name + ' failed ON :' + CONVERT(VARCHAR,Message_time)
--[Message_time],
-- [Extended_info_id],
-- [Package_name],
-- [Message_source_name],
-- [Subcomponent_name],
-- [Package_path],
-- [Execution_path],
-- LEFT([Message], 400)
FROM SSISDB.[catalog].[Event_messages] (NOLOCK)
WHERE [Package_name] = @Package_name
 AND Event_name = 'OnError'
 AND Message_time >= DATEADD(DD,0,DATEDIFF(DD,0,GETDATE()))
 AND Operation_id IN (SELECT Max(Operation_id)
 FROM SSISDB.[catalog].[Event_messages](NOLOCK)
 WHERE [Package_name] = @Package_name)
ORDER BY Message_time ASC
 
DECLARE @operator_name AS VARCHAR(100) = (SELECT top 1 name from msdb.dbo.sysoperators)
PRINT @operator_name
 
exec msdb.dbo.sp_notify_operator  @profile_name = NULL ,
    @id = NULL,
    @name = @operator_name,  
    @subject = @EMailSubject,  
    @body = @EMailBody

Here is the improved SQL Server Agent job email notification with a detailed message that we can expect now (Fig 5).