I am a SQL Server DBA. I maintain a large number of SSIS packages created by developers.
Over the weekend, many SSIS jobs failed since the jobs were attempting to insert/update values that resulted in duplicate data / key violations.
Normally, I would go through the execution report, and send a screen shot to the concerned developer. How can I create an automated process that will send an email to them about the error on packages as they occur?
You can modify the jobs to send email on failure. The problem with that approach is that you're going to know that job failed but why it failed will be a mystery until you get to a computer, VPN into your network, fire up SSMS, grab a screenshot and send it to the responsible party.
A rough framework I've used follows
This does stuff
DECLARE
@profile_name sysname = 'SQLAdmins'
, @recipients varchar(max) = '[email protected]'
, @copy_recipients varchar(max) = NULL
, @blind_copy_recipients varchar(max) = NULL
, @subject nvarchar(255) = 'failed package test'
, @body nvarchar(max) = 'Stuff has failed, fix please'
, @body_format varchar(20) = NULL
, @importance varchar(6) = 'NORMAL'
, @sensitivity varchar(12) = 'NORMAL'
, @file_attachments nvarchar(max) = NULL
, @query nvarchar(max)
= N'
SELECT
O.object_name AS FailingPackageName
, O.object_id
, O.caller_name
, O.server_name
, O.operation_id
, OM.message_time
, EM.message_desc
, D.message_source_desc
, OM.message
FROM
SSISDB.catalog.operation_messages AS OM
INNER JOIN
SSISDB.catalog.operations AS O
ON O.operation_id = OM.operation_id
INNER JOIN
(
VALUES
(-1,''Unknown'')
, (120,''Error'')
, (110,''Warning'')
, (70,''Information'')
, (10,''Pre-validate'')
, (20,''Post-validate'')
, (30,''Pre-execute'')
, (40,''Post-execute'')
, (60,''Progress'')
, (50,''StatusChange'')
, (100,''QueryCancel'')
, (130,''TaskFailed'')
, (90,''Diagnostic'')
, (200,''Custom'')
, (140,''DiagnosticEx Whenever an Execute Package task executes a child package, it logs this event. The event message consists of the parameter values passed to child packages. The value of the message column for DiagnosticEx is XML text.'')
, (400,''NonDiagnostic'')
, (80,''VariableValueChanged'')
) EM (message_type, message_desc)
ON EM.message_type = OM.message_type
INNER JOIN
(
VALUES
(10,''Entry APIs, such as T-SQL and CLR Stored procedures'')
, (20,''External process used to run package (ISServerExec.exe)'')
, (30,''Package-level objects'')
, (40,''Control Flow tasks'')
, (50,''Control Flow containers'')
, (60,''Data Flow task'')
) D (message_source_type, message_source_desc)
ON D.message_source_type = OM.message_source_type
WHERE
OM.operation_id =
(
SELECT
MAX(OM.operation_id)
FROM
SSISDB.catalog.operation_messages AS OM
WHERE
OM.message_type = 120
)
AND OM.message_type IN (120, 130);
'
, @execute_query_database sysname = NULL
, @attach_query_result_as_file bit = 0
, @query_attachment_filename nvarchar(260) = NULL
, @query_result_header bit = 1
, @query_result_width int = 256
, @query_result_separator char(1) = char(13)
, @exclude_query_output bit = 0
, @append_query_error bit = 0
, @query_no_truncate bit = 0
, @query_result_no_padding bit = 0
, @mailitem_id int = NULL
, @from_address varchar(max) = NULL
, @reply_to varchar(max) = NULL;
EXECUTE msdb.dbo.sp_send_dbmail
@profile_name
, @recipients
, @copy_recipients
, @blind_copy_recipients
, @subject
, @body
, @body_format
, @importance
, @sensitivity
, @file_attachments
, @query
, @execute_query_database
, @attach_query_result_as_file
, @query_attachment_filename
, @query_result_header
, @query_result_width
, @query_result_separator
, @exclude_query_output
, @append_query_error
, @query_no_truncate
, @query_result_no_padding
, @mailitem_id OUTPUT
, @from_address
, @reply_to;
So what'd we do? We found the last SSIS package that ran from the SSISDB that failed (message_type 120 = failed)
SELECT
MAX(OM.operation_id)
FROM
SSISDB.catalog.operation_messages AS OM
WHERE
OM.message_type = 120
We then collected relevant bits of information (all error and failure messages) about the run with the rest of the query against the SSISDB objects.
That gives enough information for someone to know the package that failed, what folder it was in, when etc. But notice that SSISDB query is assigned to a variable.
I'm going to use that variable as a parameter to sp_send_dbmail When I invoke that proc with the query parameter, I'm going to get an email telling me something broke (which is the level of info I get out of the job step) but then I have an attachment which will be the actual results of the query. Now that's pretty handy.
Left as an exercise for the user is to create a "already notified" list of operation_ids so you don't keep spamming people. Possibly another table that associates folders and or/packages with a list of recipients. This approach assumes only one package runs at a time. That's a silly assumption but twas valid when I wrote this. It would be better to have two tables: Failed and Reported. Collect the unique operation ids into the Failed table every 5 minutes. Every 30 minutes collect those IDs, send emails and move them into the Reported table. Now you have a history that you've processed them.
I'd go with email notification when job fails on the job:
The SSIS Packages have the capability of capturing their own errors, and even sending detailed emails upon success/failure.
I like the solution that @Billinkc proposed, but if you're not able to do so I'd suggest that you aid the developers in creating a more robust package which notifies you/them about package failures (and the reasons).
They can even chose to send a different email on "module(task) failure" vs "package failure" at any step in the package.
This allows the process to be a bit more modular as opposed to all-encompassing.
