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

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


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?


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:

SSMS results

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

msdb records

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:

SSIS catalog

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:

All executions report

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

All Messages report

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

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:

SSMS results from query

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

CXXXX_Call Stored Procedure_XXXX:Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.  An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "The metadata could not be determined because statement 'INSERT INTO #TXXXX (SXXXX,TXXXX,SXXXXX,LXXXX,UXXXX)  SELECT BXXXX.MXXXX' in procedure 'dXXXX' uses a temp table.".
Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Pablo Echeverria 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

Comments For This Article

Friday, December 3, 2021 - 4:46:23 PM - Pablo Echeverria Back To Top (89521)
Hi John, please provide the output of the query below:
SELECT [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

Friday, December 3, 2021 - 2:24:15 PM - John Back To Top (89518)
Unfortunately, this query no longer works.

Msg 537, Level 16, State 3, Line 16
Invalid length parameter passed to the LEFT or SUBSTRING function.
Msg 537, Level 16, State 3, Line 19
Invalid length parameter passed to the LEFT or SUBSTRING function.
Msg 536, Level 16, State 4, Line 20
Invalid length parameter passed to the RIGHT function.

get free sql tips
agree to terms