Find Detailed Error Messages for SQL Server Agent Job Step History for an SSIS Package

By: Pablo Echeverria   |   Comments (2)   |   Related: > Integration Services Administration

Problem

It's a common task we all have been asked to do: check the history of a SQL Server Agent Job. You can view the packages that failed using this tip, which creates a T-SQL command that allows you to view the latest history records from the [msdb] database. But what happens when one of the steps is from an SQL Server Integration Services (SSIS) package that uses the SSIS catalog as the source? Then you don't know what happened or what failed. Is there a script that can give you the information you're interested in, and that is easy to run?

Solution

First, we're going to visualize the problem by using SQL Server Management Studio (SSMS) to view the job history, it doesn't tell us what happened or what failed:

If we view the latest records from msdb for the job steps this doesn't help either:

It tells you to go to the Integration Services Catalog and open the [All Executions] report. You need to identify the job name, job step, and the package it is executing, then go to the Integration Services Catalogs and locate it, then open the "All Executions" report:

By doing it this way, it fills the filters for you, as you can see below. From there, you need to view the "All Messages" report:

And navigate through several pages of information, visually checking what happened:

Note that the report doesn't allow you to copy the text and if you export the report using the "Print" button at the top, it is going to contain all messages, not just the ones you're interested in.

Fortunately, there is a script that allows you to view the records easily. In the script below, you only need to specify the name of the job in @JobName, and the step number in @StepId.

DECLARE @JobName SYSNAME, @StepId INT, @Command NVARCHAR(MAX), @PosStart BIGINT, @PosEnd BIGINT, @Server NVARCHAR(128)

-- set these values to find the error message
SET @JobName = 'YourJobName'
SET @StepId = 1

USE [msdb]
SELECT @Command = [s].[command]
FROM [sysjobs] [j]
INNER JOIN [sysjobsteps] [s] ON [s].[job_id] = [j].[job_id]
WHERE [j].[name] = @JobName
  AND [s].[step_id] = @StepId

SET @PosStart = PATINDEX('%/SERVER %', @Command)+8
SET @PosEnd = CHARINDEX(' ', @Command, @PosStart)
SET @Server = SUBSTRING(@Command, @PosStart, @PosEnd - @PosStart)
SET @PosStart = PATINDEX('%"\"%', @Command)+3
SET @PosEnd = PATINDEX('%\""%', @Command)
SET @Command = SUBSTRING(@Command, @PosStart, @PosEnd - @PosStart)
SET @Command = RIGHT(@Command, CHARINDEX('\', REVERSE(@Command)) - 1)

PRINT '--This command must be run in ' + @Server + '
USE [SSISDB]
SELECT TOP 10 [message_time], [message], [event_name], [message_source_name], [subcomponent_name]
FROM [catalog].[event_messages]
WHERE [package_name] = ''' + @Command + '''
  AND [event_name] IN (''OnWarning'', ''OnError'')
ORDER BY [event_message_id] DESC'

USE [SSISDB]
SELECT TOP 10 [message_time], [message], [event_name], [message_source_name], [subcomponent_name]
FROM [catalog].[event_messages]
WHERE [package_name] = @Command
  AND [event_name] IN ('OnWarning', 'OnError')
ORDER BY [event_message_id] DESC

It works in the following way:

1. It gets the command that is executed, using the specific job name and step id.
2. Extracts the server name where the package is deployed (if the SSISDB is in a different server), and the package name without the full path.
3. Prints the script that is executed in the "messages" window, in case you need to run it in another server (where the SSISDB database is located).
4. Executes the script, in case the SSISDB database that contains the package is on the same server as the SQL Server Agent job.

Note that the script will only work for a SSIS package job step that uses the SSIS catalog as the source. In all other cases, it will throw a lot of errors or not display anything at all.

In case the SSISDB database that contains the package is on the same server as the SQL Server Agent job, it is going to give you the results, which you can export to Excel and copy only the messages you're interested in:

Here is an example record and you can see the reason why it failed easily:

Next Steps

• Check this tip to identify the jobs that have had problems in the past.
• Check the official documentation about the SSIS catalog.
• Check this tip about the configuration of the SSIS catalog.
• Check this article about the different types of SSIS package job steps.
• Check out the SQL Server Agent Tips.
• Check out the SQL Server Integration Services Tutorial.

About the author

Pablo Echeverria is a talented database administrator and C#.Net software developer since 2006. Pablo wrote the book "Hands-on data virtualization with Polybase". He is also talented at tuning long-running queries in Oracle and SQL Server, reducing the execution time to milliseconds and the resource usage up to 10%. He loves learning and connecting new technologies providing expert-level insight as well as being proficient with scripting languages like PowerShell and bash. You can find several Oracle-related tips in his LinkedIn profile.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips