Send Meaningful Errors for SSIS Packages

Recently, Kevin Hill (b | t ) posted on getting package errors from the SSIS catalog in a single query as opposed to clicking through the SSIS Reports and digging through pages.  I took that and ran with it a little bit. The first pass needed an additional index on the catalog to increase performance.  Kevin’s included that at the bottom of his query on the post above.  (You probably don’t need the included “message” column, though.)

I wanted to take this and run with it a little bit to report on all errors for a given folder within the last day, then e-mail that in an HTML formatted e-mail. To that end, I wrote up a quick stored procedure that should take the Folder or Package or Project name and a “to” e-mail address to send an e-mail through DBMail.  I’m assuming a profile set up for “Default” within DBMail that can be used. If not, feel free to change that in the code.

This is defined just to pick up all errors within the last 24 hours and not trying to be incredibly robust to work with combinations of folder/project/path. All of our needs are for the Folder level so I haven’t tried to enhance this to work with combinations of the names. Code for the stored procedure is below:


–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

 

No Responses

Leave a Reply

Your email address will not be published. Required fields are marked *