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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 |
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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 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; 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 :-)