SQL Server Integration Services (SSIS) is a powerful tool for data integration and transformation. However, when it comes to error reporting in SQL Server Agent jobs that involve SSIS packages, the default behavior can be less than ideal. In this blog post, we will explore how the SSISDB database (also known as the Integration Services catalog) can be used to improve error reporting in SQL Server Agent jobs.

The Problem

Prior to SQL Server 2012, when an SSIS package failed in a SQL Server Agent job, the error message provided in the email notification was specific and informative. It would include the error from the SSIS execution engine, allowing users to quickly identify and address the issue. However, with the introduction of the SSISDB catalog in SQL Server 2012, Microsoft decided to replace the specific SSIS error with a generic message that directed users to the SSIS Catalog error log.

This change in behavior can be frustrating for users who rely on email notifications to quickly identify and react to failed jobs. The generic message provides no details about the error, requiring users to access SSMS and run a custom SSRS report to view the error details. This extra step can be time-consuming and inconvenient, especially when accessing emails on mobile devices without immediate access to a computer.

The Solution

To address this issue and improve error reporting in SQL Server Agent jobs with SSIS packages, we can use a T-SQL query to connect tables and records between the MSDB and SSISDB databases. This query will retrieve a detailed error message for a failed SSIS package, allowing users to react without leaving their current location.

The solution involves the following steps:

1. Find the job name based on the current SPID.
2. Find a job with a failed step.
3. Connect the step with the SSIS package name.
4. Use the SSIS package name to find runtime errors in the SSISDB catalog.
5. Retrieve a detailed error message.
6. Send a detailed error report to a default job operator.

The provided T-SQL script can be added as the last step in a job, with all the steps that include SSIS packages leading to that last step on failure. This script will retrieve the error details and send them to the specified job operator.

Example Script

DECLARE @Full_ssis_command VARCHAR(4000),
 @Job_step_id INT,
 @Package_name VARCHAR(4000),
 @EMailBody NVARCHAR(2000),
 @EMailSubject VARCHAR(150),
 @Job_id UNIQUEIDENTIFIER

SELECT @Job_id = Job_id
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
AND [program_name] IS NOT NULL 
AND CHARINDEX('(Job 0x', ions.[program_name], 1) > 0

IF @Job_id IS NOT NULL
BEGIN
    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

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

    EXEC msdb.dbo.sp_notify_operator @profile_name = NULL,
    @id = NULL,
    @name = @operator_name,  
    @subject = @EMailSubject,  
    @body = @EMailBody
END

With this solution in place, users will receive email notifications with detailed error messages when an SSIS package fails in a SQL Server Agent job. This allows for quicker identification and resolution of issues, without the need to access SSMS or run custom reports.

Conclusion

The SSISDB database provides a robust framework for managing and monitoring SQL Server Integration Services packages. By leveraging the SSISDB catalog, we can improve error reporting in SQL Server Agent jobs that involve SSIS packages. The provided T-SQL script allows for detailed error messages to be sent via email, enabling users to react promptly and efficiently to failed jobs.

By implementing this solution, users can save time and effort in troubleshooting and resolving issues related to SSIS packages in SQL Server Agent jobs.