Reporting for SSIS Errors
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
--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