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:
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 |
About Author
paschott
I’ve been working with SQL Server for quite some time. Along the way, I’ve learned quite a few things and realized that I won’t ever know everything about SQL Server. I intended to keep growing and learning to be able to do my job well and share my experiences with others. I currently work for a Health-related non-profit based in Boise as a Database Architect.