SSIS: Check for Errors in SSISDB and Send Email


SSISDB is the database that is collaborating with SSIS. When we execute a package, the execution information is logged in SSISDB. If a job is scheduled, we need to know if there are errors in the executions. As a last step in SSIS package, I run stored procedure that searches in SSISDB for errors for this execution and sends email.

Create database

USE [master];
GO

IF (DB_ID('PL') IS NOT NULL)
	BEGIN
		ALTER DATABASE PL SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
		DROP DATABASE PL;
	END
GO

CREATE DATABASE PL;
GO

USE PL;
GO

Create Database Mail Profile
USE [master];
GO

DECLARE @Password NVARCHAR(128) = 'MyStrongPassword';

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'Database Mail XPs', 1;
RECONFIGURE;
GO

-- CREATE Mail [Admin@localhost]
IF NOT EXISTS (SELECT 1 FROM msdb.dbo.sysmail_profile WHERE [name] = 'Admin@localhost')
	BEGIN
		-- CREATE Profile [Admin@localhost]
		EXEC msdb.dbo.sysmail_add_profile_sp
			@profile_name = 'Admin@localhost'
			, @description = 'Use local XAMPP (MercuryMail) and account Admin@localhost';
	END -- IF EXISTS profile
 
IF NOT EXISTS (SELECT 1 FROM msdb.dbo.sysmail_account WHERE [name] = 'Admin@localhost')
	BEGIN
		-- CREATE Account [Admin@localhost]
		EXECUTE msdb.dbo.sysmail_add_account_sp
			@account_name = 'Admin@localhost'
			, @email_address = 'Admin@localhost'
			, @display_name = 'Admin (localhot)'
			, @replyto_address = 'Admin@localhost'
			, @description = 'Use local XAMPP (MercuryMail) and account Admin@localhost'
			, @mailserver_name = 'localhost'
			, @mailserver_type = 'SMTP'
			, @port = '25'
			, @username = 'Admin@localhost'
			, @password =  @Password 
			, @use_default_credentials = 0
			, @enable_ssl = 0;
	END -- IF EXISTS account

IF NOT EXISTS
(
	SELECT *
	FROM
		msdb.dbo.sysmail_profileaccount AS PA
		JOIN msdb.dbo.sysmail_profile AS P
			ON PA.profile_id = P.profile_id
		JOIN msdb.dbo.sysmail_account AS A
			ON PA.account_id = A.account_id
	WHERE P.[name] = 'Admin@localhost'
	AND A.[name] = 'Admin@localhost'
)
 BEGIN
	-- Associate Account [Admin@localhost] to Profile [Admin@localhost]
	EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
		@profile_name = 'Admin@localhost'
		, @account_name = 'Admin@localhost'
		, @sequence_number = 1 ;
 END -- IF EXISTS associate accounts to profiles

-- DELETE Mail [Admin@localhost]
/*
IF EXISTS
(
	SELECT 1
	FROM
		msdb.dbo.sysmail_profileaccount AS PA
		JOIN msdb.dbo.sysmail_profile AS P
			ON PA.profile_id = P.profile_id
		JOIN msdb.dbo.sysmail_account AS A
			ON PA.account_id = A.account_id
	WHERE P.[name] = 'Admin@localhost'
	AND A.[name] = 'Admin@localhost'
)
	BEGIN
		EXEC msdb.dbo.sysmail_delete_profileaccount_sp
			@profile_name = 'Admin@localhost'
			, @account_name = 'Admin@localhost';
	END

IF EXISTS (SELECT 1 FROM msdb.dbo.sysmail_account WHERE [name] = 'Admin@localhost')
	BEGIN EXEC msdb.dbo.sysmail_delete_account_sp @account_name = 'Admin@localhost'; END

IF EXISTS(SELECT * FROM msdb.dbo.sysmail_profile WHERE name = 'Admin@localhost')
	BEGIN EXEC msdb.dbo.sysmail_delete_profile_sp @profile_name = 'Admin@localhost'; END
*/

Create stored procedure to check for errors and send email
USE SSISDB;
GO

-- Clean up
IF (OBJECT_ID(N'dbo.SendEmailOnError', N'P') IS NOT NULL)
	BEGIN DROP PROC dbo.[SendEmailOnError]; END
GO

CREATE PROC dbo.[SendEmailOnError]
	@ServerExecutionID BIGINT
	, @MachineName NVARCHAR(128)
	, @PackageName NVARCHAR(128)
	, @ProfileName NVARCHAR(128)
	, @Recipients VARCHAR(MAX)
	, @FromAddress VARCHAR(MAX)
	, @ReplyTo VARCHAR(MAX)

AS
BEGIN

/*=============================================
Author:				Peter Lalovsky
Create date:	2017-12-29
Description:	Execute SQL Task in SSIS calls this SP.
							Check in SSISDB if there are errors in @ServerExecutionID and sends an email to operator.
=============================================*/

SET NOCOUNT ON;

WAITFOR DELAY N'00:00:05';

DECLARE
	@Subject NVARCHAR(128) = CONCAT(N'SSIS errors: Machine Name - ', @MachineName, N'; Package Name - ', @PackageName)
	, @HTMLBody NVARCHAR(MAX);

IF EXISTS
(
	SELECT DISTINCT 1
	FROM internal.operation_messages
	WHERE
		operation_id = @ServerExecutionID
		AND message_type = 120
)
BEGIN
	SET @HTMLBody = N'
<html>
<head>
<style>
	table {	border-collapse: collapse; }

	table, th, td {
		font-family: Verdana, Arial, Helvetica;
		font-size: 11px;
		border: 1px solid #999;
		vertical-align: top;
		padding: 5px;
	}
	th { background-color: #DEDEDE; }
</style>
</head>
<body>
		';

	SELECT @HTMLBody += 
		CONVERT(NVARCHAR(MAX)
		, (SELECT
		(
			SELECT
				N'Server Execution ID' AS th
				, N'Folder Name' AS th
				, N'Project Name' AS th
				, N'Package Name' AS th
				, N'Server Name' AS th
				, N'Machine Name' AS th
				, N'Message Time' AS th
				, N'Message Source Name' AS th
				, N'Message' AS th
				, N'Subcomponent Name' AS th
				, N'Package Path' AS th
				, N'Execution Path' AS th
			FOR XML RAW(N'tr'), ELEMENTS, TYPE
		) AS N'thead'
		, (
			SELECT
				CE.execution_id AS td
				, CE.folder_name AS td
				, CE.project_name AS td
				, CE.package_name AS td
				, CE.server_name AS td
				, CE.machine_name AS td
				, CONVERT(CHAR(19), CEM.message_time, 120) AS td
				, CEM.message_source_name AS td
				, CEM.[message] AS td
				, CEM.subcomponent_name AS td
				, CEM.package_path AS td
				, CEM.execution_path AS td
			FROM
				[catalog].executions AS CE
				INNER JOIN [catalog].event_messages AS CEM
					ON CE.execution_id = CEM.operation_id
			WHERE
				CE.execution_id = @ServerExecutionID
				AND CEM.event_name = N'OnError'
			FOR XML RAW(N'tr'), ELEMENTS, TYPE
		) AS N'tbody'
		FOR XML PATH(''), ROOT(N'table')));

	SET @HTMLBody += N'
</body>
</html>
		';

	EXEC msdb.dbo.sp_send_dbmail
		@profile_name = @ProfileName
		, @recipients = @Recipients
		, @subject = @Subject
		, @body = @HTMLBody
		, @body_format = N'HTML'
		, @from_address = @FromAddress
		, @reply_to = @ReplyTo;
END

END

GO

You can download the SSIS project that simulates an error and sends email here.

To collect the log in SSISDB, we need to deploy the package on the server in SSISDB. In Visual Studio right click on the project and Deploy

Deploy to SSISDB 1

Deploy to SSISDB 2

In SSMS right click on the package and Execute…

Execute SSIS Package on SSISDB

And check your email:

Email with the Error Log

Now we know that we need to take action.

Keep it simple :-)