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