How do I get a report on the execution of an SSIS Package

If you want an execution report of an SSIS package, you can use the code below. It will generate a dataset for the last execution report of the SSIS package you want.

I think the toggles speak for themselves but just in case, here’s a little overview.

PackageName: Yes, this is the name of the package you want a report on
Verbose: This will show as much info as possible
ShowValidationMessages: Shows validation messages
ShowErrorMessages: Shows error messages. Turn all the other SHOW options off and this on to only see error messages
ShowInfoMessages: Shows info messages.
ShowAuditMessages: Shows audit messages
ShowLatestMessagesFirst: Shows the latest messages first

--8<----------------------------------------------------------------------------
/*

Author:			Kristof Elst
Description:	Shows all messages of a certain type for an SSIS package
				Useful on environments where you can't check the execution reports
Version:		1.0.0
Date:			2020-09-21

*/
--8<----------------------------------------------------------------------------

USE SSISDB

DECLARE @PackageName NVARCHAR(MAX) = 'somepackage.dtsx'
DECLARE @LastOperationId BIGINT = (SELECT MAX(operation_id) FROM catalog.event_messages WHERE package_name = @PackageName)
DECLARE @Verbose BIT = 1
DECLARE @ShowValidationMessages BIT = 1
DECLARE @ShowErrorMessages BIT = 1
DECLARE @ShowInformationMessage BIT = 1
DECLARE @ShowAuditMessages BIT = 0
DECLARE @ShowLatestMessagesFirst BIT = 1

SELECT
	event_message_id,
	operation_id,
	message_time,
	elapsed_time = FORMAT(CAST(message_time AS DATETIME) - CAST(LAG(message_time, 1) OVER (ORDER BY operation_id) AS DATETIME), 'HH:mm:ss:ms'),
	message_type,
	message_source_type,
	message,
	extended_info_id,
	package_name,
	event_name,
	message_source_name,
	message_source_id,
	subcomponent_name,
	package_path,
	execution_path,
	threadID,
	message_code
FROM
	catalog.event_messages EVM
WHERE
	operation_id = @LastOperationId
AND	(	@Verbose = 1
	OR	(
			(
				@ShowValidationMessages = 1
			AND message_type in (10,20)
			)
		OR	(
				@ShowErrorMessages = 1
			AND message_type in (120)
			)
		OR	(
				@ShowInformationMessage = 1
			AND message_type in (40)
			)
		OR	(
				@ShowAuditMessages = 1
			AND message_type in (60)
			)
		)
	)
ORDER BY
	operation_id, 
	event_message_id * IIF(@ShowLatestMessagesFirst=1,-1,1)

--8<----------------------------------------------------------------------------

Author: Kristof Elst

Description: Shows all messages of a certain type for an SSIS package

Useful on environments where you can't check the execution reports

Version: 1.0.0

Date: 2020-09-21

--8<----------------------------------------------------------------------------

USE SSISDB

DECLARE @PackageName NVARCHAR(MAX) = 'somepackage.dtsx'

DECLARE @LastOperationId BIGINT = (SELECT MAX(operation_id) FROM catalog.event_messages WHERE package_name = @PackageName)

DECLARE @Verbose BIT = 1

DECLARE @ShowValidationMessages BIT = 1

DECLARE @ShowErrorMessages BIT = 1

DECLARE @ShowInformationMessage BIT = 1

DECLARE @ShowAuditMessages BIT = 0

DECLARE @ShowLatestMessagesFirst BIT = 1

SELECT

event_message_id,

operation_id,

message_time,

elapsed_time = FORMAT(CAST(message_time AS DATETIME) - CAST(LAG(message_time, 1) OVER (ORDER BY operation_id) AS DATETIME), 'HH:mm:ss:ms'),

message_type,

message_source_type,

message,

extended_info_id,

package_name,

event_name,

message_source_name,

message_source_id,

subcomponent_name,

package_path,

execution_path,

threadID,

message_code

FROM

catalog.event_messages EVM

WHERE

operation_id = @LastOperationId

AND ( @Verbose = 1

OR (

(

@ShowValidationMessages = 1

AND message_type in (10,20)

)

OR (

@ShowErrorMessages = 1

AND message_type in (120)

)

OR (

@ShowInformationMessage = 1

AND message_type in (40)

)

OR (

@ShowAuditMessages = 1

AND message_type in (60)

)

ORDER BY

operation_id,

event_message_id * IIF(@ShowLatestMessagesFirst=1,-1,1)