SSIS execution error email 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


sp_send_dbmail
SSISDB views
sql agent running periodically


This does stuff

DECLARE
        @profile_name sysname = 'SQLAdmins'
,       @recipients varchar(max) = 'billinkc@friedChicken.com'
,       @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.