How to setup SQL Agent Job alerts to include SSIS catalog errors

I must have been one of the first people who abandoned BIDS as soon as SSDT was first introduced. Although I have never regretted that decision, I do sometimes feel that SSDT has introduced an unnecessary additional layer of troubleshooting package execution failures. Regardless of whether your SSIS packages are deployed using BIDS or SSDT, the common scheduling mechanism used to run those packages is usually the SQL Server Agent. This is where you are likely to encounter additional layers of troubleshooting SSDT-deployed packages. This is because unlike BIDS where package execution details were stored in the same database (msdb) as were the execution details of SQL Server Agent job, the advent of SSDT came with an introduction a new database – SSISDB – which is used to, amongst other things, store package execution details. Subsequently, in order to retrieve details of package execution, we ought to query the SSIDB database.

To illustrate such an unnecessary inconvenience, let’s say I have an SSIS package – PackageTest.dtsx – that is running inside a SQL Server Agent job and makes use of an Execute SQL Task to run a T-SQL code depicted in Script 1:

SELECT 1'

Script 1: T-SQL statement with incorrect syntax

If I deploy this package via BIDS and then execute it using a fictitious SQL Server Agent job, the package execution fails as expected (due to an unclosed quotation mark) and the details of the failure are provided in the SQL Server Agent job history log, as shown in Figure 1.

Figure 1: SQL Server Agent job error details after BIDS package execution

Then, it is easy for me to set up a custom notification service (using an msdb.dbo.sp_send_dbmail system stored procedure) that will, upon job failure, iterate through a given job’s history and send out emails to inform and provide support developers the details of the package error, as per the sample email shown in Figure 2.

Figure 2: Sample failure email notification of a BIDS-deployed package

Unfortunately, replicating the same notification process against SSDT deployed packages is not so straightforward. Say for instance I deploy the same package I mentioned above but this time around I deploy it into the Integration Services Catalog using SSDT, and run it within a SQL Server Agent job. Well, although the definition of the package is still the same, I am now getting a different error message in SQL Agent job, as shown in Figure 3.

Figure 3: SQL Server Agent job error details following execution of SSDT package

In fact, compared to the error message logged in Figure 1, the error message logged in Figure 3 is shorter as I am advised to rather look for details of the package execution under Integration Services Catalog’s All Executions report. This means that I may now have to grant whoever will be troubleshooting this error (it could be consultants or part-time staff sitting around the world), access to the production instance of the SSISDB in order to view package execution details.

How to retrieve SSIS catalog error messages

When dealing with Integration Services Catalog based packages, we clearly need to introduce a custom SQL Server Agent job step that will retrieve details of the package execution error and send them out to the relevant recipients – without having people go through the All Executions report!

Get Latest Execution ID

Every Integration Services Catalog execution is assigned an Execution ID. This Execution ID can be retrieved from the [SSISDB].[internal].[executions] table. Because this table stores executions for all packages, we need filter it either by a package name or a project name. The complete script on how to retrieve the latest Execution ID is given in Script 2.

SELECT MAX([execution_id]) [execution_id]

FROM [SSISDB].[internal].[executions] (NOLOCK)

WHERE [package_name] = 'PackageTest.dtsx'

Script 2: Retrieve latest Execution ID

Retrieve Error Messages

With the latest Execution ID in hand, we now need to retrieve all error messages relating to that latest Execution ID. There are two options we can go about doing this; one would involve joining several tables including [SSISDB]. [internal].[operation_messages] whilst another option involves querying a built-in Integration Services Catalog view –[SSISDB].[catalog].[event_messages], in this discussion we have opted to query the view.

Similarly to the executions table, the [SSISDB].[catalog].[event_messages] table contains all messages relating Integration Services Catalog events and executions. Therefore we should rather reduce the dataset from this view by applying two filters; the Execution ID and OnError event type. The complete script that retrieves package execution messages is shown in Script 3:

SELECT

event_message_id

, event_name

, message_time

, [message]

, message_source_name

, execution_path

FROM [SSISDB].[catalog].[event_messages] em

WHERE em.operation_id = (

SELECT MAX([execution_id])

FROM [SSISDB].[internal].[executions] (nolock)

where [package_name] = 'PackageTest.dtsx'

)

AND event_name NOT LIKE '%Validate%'

AND event_name = 'OnError'

Script 3

A preview of the results of Script 3 execution are shown in Figure 4 and you would notice that they closely resemble details provided in the All Executions report shown in Figure 5.

Figure 4

Figure 5: Details of the All Executions Report

Configure error notification Job step

Now that we have retrieved the error messages, we can proceed to customize our error SQL Server Agent job to include a job step that will send out the error messages to our support developers/DBA. The simplest way of implementing such a step is to have it execute a stored procedure.

The definition of the stored procedure is too long to provide in this article instead I have included a download location under the Downloads location at the bottom of this article. Nevertheless, looking at the preview of the spSendEmailISCatalog stored procedure shown in Figure 6, we can see that it only requires two parameters; email addresses of recipients as well as an email subject line. However, feel free to customize the stored procedure according to your preference.

Figure 6: Definition of spSendEmailISCatalog stored procedure

Figure 7 shows an updated job step list of our sample job that now includes the send out error email job step name.

Figure 7: Steps of sample SQL Server Agent job

Following the changes to the job, Figure 8 shows an email alert that now successfully includes the error details of an SSDT-deployed package.

Figure 8: Alert email notification displaying error messages from SSISDB

SSIS Reporting pack

Jamie Thomson, one of leading bloggers on topics relating to SSIS, has a free-to-download SSIS Reporting Pack that contains a list of SSRS reports that are similar to the Integration Services Catalog’s built-in reports – including the All Executions report. Thus, instead of introducing a custom error notification job step in all your jobs as covered in this article, you could also just restore the SSRS reports found in the SSIS Reporting Pack and have people go through those reports instead of granting them access to reports available from the Integration Services Catalog dashboard. You will still need an account that has access to the SSISDB to run those reports.

Conclusion

In this article, we have covered different logging by SQL Server Agent jobs of error messages pertaining to package execution failures. It was illustrated how SQL Server Agent job’s error reporting of BIDS-deployed packages is usually more verbose compared to SSDT-deployed packages. Finally, an alternative custom error notification job step using a stored procedure that queries SSISDB catalog views and tables was provided.

Downloads

spSendEmailISCatalog stored procedure

Sifiso Ndlovu

Sifiso is Data Architect and Technical Lead at SELECT SIFISO – a technology consulting firm focusing on cloud migrations, data ingestion, DevOps, reporting and analytics. Sifiso has over 15 years of across private and public business sectors, helping businesses implement Microsoft, AWS and open-source technology solutions. He is the member of the Johannesburg SQL User Group and also hold a Master’s Degree in MCom IT Management from the University of Johannesburg.

Sifiso's LinkedIn profile

View all posts by Sifiso W. Ndlovu