Reporting for SSIS Errors
--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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment