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

By:   |   Updated: 2019-03-29   |   Comments   |   Related: More > 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

Last Updated: 2019-03-29

get scripts

next tip button

About the author
MSSQLTips author Pablo Echeverria I've worked for more than 10 years as a software programmer and analyst. In 2016, I switched jobs to a DBA position, where I have implement new processes, created better monitoring tools and grown my data scientist skills.

View all my tips

Comments For This Article


Recommended Reading

Copy or Duplicate SQL Server Integration Services (SSIS) Environments using T-SQL

Find SSIS Package Stuck in Running Status

Reporting with the SQL Server Integration Services Catalog

SSIS Catalog Deployment to Support Dev, QA and Production

Automated Inventory Collection of Scheduled SQL Server Integration Services Packages

get free sql tips
agree to terms

Learn more about SQL Server tools