Introduction

The SSISDB database (a.k.a. the Integration Services catalog) was introduced back in SQL Server 2012 as a way to de-clutter the 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 notifications as part of the SSISDB Catalog, specifically the error message reporting on failed packages.

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 a specific SSIS error 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 whether 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 an 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 this email notification. Now you don't have that option. You would need a real computer to fire-up SSMS and run a custom SSRS report 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

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 a 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 a step with SSIS package name
4. Use the SSIS package name to find run-time errors in the SSISDB catalog
5. Bubble up to an inner exception to include only detailed error information
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 will now precede that step. It will need to quit the job reporting success (Fig 4) instead of continuing to the next step.

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
fromsys.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)
where1=1
AND ions.session_id = @@SPID --63
AND [program_name] is not null 
AND CHARINDEX('(Job 0x',ions.[program_name],1) > 0
--print @Job_name
IF @Job_id IS NOT NULL
BEGIN
SELECTTOP 1 @Job_step_id = Step_id
FROMmsdb.dbo.sysjobhistory (NOLOCK)
WHERERun_status <> 1
 AND Step_id > 0
 AND Job_id = @Job_id
ORDER BY Instance_id DESC
SELECT@Full_ssis_command = Command
FROMmsdb.dbo.sysjobsteps (NOLOCK)
WHEREJob_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)
FROMSSISDB.[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
END

Here is the email with a detailed message that we can expect now (Fig 5).

Assumptions:

1. DatabaseMail is enabled and functioning
2. DatabaseMail has a public and default mail profile
3. There is at least one configured Operator

Disclaimer

This blog post is partially based on the following blog posts/forum discussions: https://www.sqlservercentral.com/forums/topic/identifying-sql-agent-job-name-based-on-the-job-id https://thebakingdba.blogspot.com/2012/11/sql-server-2012-ssis-getting-useful.html