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

USE [master];

IF (DB_ID('PL') IS NOT NULL)

BEGIN

ALTER DATABASE PL SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

DROP DATABASE PL;

END

CREATE DATABASE PL;

USE PL;

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
*/

USE [master];

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

EXEC sp_configure 'show advanced options', 1;

RECONFIGURE WITH OVERRIDE;

EXEC sp_configure 'Database Mail XPs', 1;

RECONFIGURE;

-- 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

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

USE SSISDB;

-- Clean up

IF (OBJECT_ID(N'dbo.SendEmailOnError', N'P') IS NOT NULL)

BEGIN DROP PROC dbo.[SendEmailOnError]; END

CREATE PROC dbo.[SendEmailOnError]

@ServerExecutionID BIGINT

, @MachineName NVARCHAR(128)

, @PackageName NVARCHAR(128)

, @ProfileName NVARCHAR(128)

, @Recipients VARCHAR(MAX)

, @FromAddress VARCHAR(MAX)

, @ReplyTo VARCHAR(MAX)

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

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

In SSMS right click on the package and Execute…

And check your email:

Now we know that we need to take action.

Keep it simple :-)