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
Fig #1 – SSISDB Database and Catalog

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 a specific SSIS error with a generic message directing the user to the SSIS Catalog error log (see Fig #2).

Fig #2 – The job failed

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 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 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 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).

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.

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 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