Reporting for SSIS Errors

pr_SendSSISErrors.sql

--Note that this uses a DB Mail Profile named "Default".
CREATE PROC dbo.pr_SendSSISErrors
	@FolderName sysname = NULL,
	@ProjectName sysname = NULL,
	@PackageName sysname = NULL,
	@ToAddress NVARCHAR(500)
AS
BEGIN --Proc
SET NOCOUNT ON

--Query for SSIS Errors:
/*
Created by Kevin Hill, Dallas DBAs LLC, 12/28/2018
Inspired by work from Jules Behrens
 
This queries multiple SSISDB tables to return a clear path from Top to bottom 
related to errors in an Integration Services Catalog based SSIS package.
It has not yet been tied back to job execution, nor is it set to email info out.
 
Use this as a backup to your normal job failure checks to tie it all together
instead of spending a full cup of coffee clicking and drilling into the cumbersome 
All Executions report.
 
Free to use and modify, please leave this header as a courtesy.
*/

	DECLARE @EmailSubject nvarchar(255)

	--Errors by Folder
	IF @FolderName IS NOT NULL
	SELECT @EmailSubject = 'SSIS Errors for Folder: ' + @FolderName
	----Errors by Project
	IF @ProjectName IS NOT NULL
	SELECT @EmailSubject = 'SSIS Errors for Project: ' + @ProjectName
	----Errors by Package
	IF @PackageName IS NOT NULL
	SELECT @EmailSubject = 'SSIS Errors for Package: ' + @PackageName

DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
		N'<H1>SSIS Error Messages</H1>' +
		N'<table border="1">' +
		N'<tr><th>Folder Name</th><th>Project Name</th><th>Package Name</th>' +
		N'<th>Message Source</th><th>Message</th><th>Message Time</th></tr>' +
		CAST ( ( SELECT td = fold.[name], '',
					   td = proj.[name], '',
					   td = pack.[name], '',
					   td = mess.[message_source_name], '',
					   td = mess.[message], '',
					   td = mess.[message_time], ''
				--,mess.[execution_path] -- this is a pretty long path if you are pasting into an email or Excel
				FROM [catalog].[projects] proj
				JOIN [catalog].[packages] pack
					ON proj.project_id = pack.project_id
				JOIN [catalog].[folders] fold
					ON fold.folder_id = proj.folder_id
				JOIN [catalog].[executions] execs
					ON execs.folder_name = fold.[name]
						AND execs.project_name = proj.[name]
						AND execs.package_name = pack.[name]
				JOIN [catalog].[operations] ops
					ON execs.execution_id = ops.operation_id
				JOIN [catalog].[event_messages] mess
					ON ops.[operation_id] = mess.[operation_id]
				WHERE mess.message_type IN ( 120 ) -- errors only
					--and mess.message_type in (120,130)  -- errors and warnings
					AND mess.message_time > GETDATE() - 1 -- adjust as necessary
					AND (fold.name = @FolderName -- errors by folder
						 OR proj.name = @ProjectName -- errors by project
						 OR pack.name = @PackageName
						) -- errors by package
				FOR XML PATH('tr'), TYPE 
		) AS NVARCHAR(MAX) ) +
N'</table>' ;


EXEC msdb.dbo.sp_send_dbmail
		@profile_name = 'Default' ,
		@recipients = @ToAddress,
		@subject = @EmailSubject,
		@body = @tableHTML,
		@body_format = 'HTML' ;

END --Proc
GO