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.
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.
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:
- Find the job name based on a current SPID
- Find a job with a failed step
- Connect a step with SSIS package name
- Use the SSIS package name to find run-time errors in the SSISDB catalog
- Bubble up to an inner exception to include only detailed error information
- Locate a default job operator
- 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).
- DatabaseMail is enabled and functioning
- DatabaseMail has a public and default mail profile
- There is at least one configured Operator
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