Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


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

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

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


next webcast button


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




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools