SSIS Error Log Notification

USE msdb

GO

DECLARE

          @ToEmail VARCHAR(1000) = '',

          @CCEmail VARCHAR(1000) = '',

          @minute INT = null

 

SET NOCOUNT ON

DECLARE c4 cursor for

SELECT  id , event , computer , operator , source , sourceid ,       executionid , starttime , endtime , message  FROM sysssislog

WHERE executionid in (SELECT executionid FROM sysssislog WHERE event = 'OnError' )

and starttime > dateadd(mi, -@minute, getdate())

ORDER BY executionid, id

 

open c4

DECLARE @id INT,

              @event VARCHAR(256),

              @computer VARCHAR(256),

              @operator VARCHAR(256),

              @source VARCHAR(256),

              @sourceid uniqueidentifier,

              @executionid uniqueidentifier,

              @starttime datetime,

              @endtime datetime,

              @message VARCHAR(1024),

              @errormsg VARCHAR(4000)

 

DECLARE @startid INT,

              @cur_package VARCHAR(256),

              @endid INT,

              @pre_id INT,

              @start_time datetime,

              @end_time datetime,

              @cmd VARCHAR(8000)

 

DECLARE @subject1 VARCHAR(256)

 

SET @errormsg = ''

SET @cmd = ''

fetch next FROM c4 INTo @id , @event , @computer , @operator , @source , @sourceid , @executionid , @starttime , @endtime , @message

while @@fetch_status = 0

begin

          if @message like 'End of package execution.%'

          begin

                   SET @endid = @id

                   SET @end_time = @endtime

                  

                   SELECT @startid = id FROM sysssislog WHERE executionid = @executionid and

                                         event = 'PackageStart' and message like 'Beginning of package execution.%'

                   SELECT @start_time = starttime FROM sysssislog WHERE executionid = @executionid and

                                         event = 'PackageStart' and message like 'Beginning of package execution.%'

                   SELECT @errormsg = @errormsg + message FROM sysssislog WHERE id between @startid and

                                         @endid and executionid = @executionid

                  

                   SET @subject1 = 'SSIS Package ' + @source + ' Failed on ' + @@SERVERNAME

                  

                   SELECT @cmd = @cmd + 'SQL Instance: ' + @@SERVERNAME + CHAR(10)

                   SELECT @cmd = @cmd + 'Package Name: ' + @source + CHAR(10)

                   SELECT @cmd = @cmd + 'Job Originating Host: ' + @computer + CHAR(10)

                   SELECT @cmd = @cmd + 'Run As: ' + @operator + CHAR(10)

                   SELECT @cmd = @cmd + 'Start DT: ' + convert(VARCHAR(30),@start_time,121) + CHAR(10)

                   SELECT @cmd = @cmd + 'End DT: ' + convert(VARCHAR(30),@end_time,121) + CHAR(10)

                   SELECT @cmd = @cmd + 'Error Message: '+ CHAR(10) + @errormsg

 

                             exec  msdb.dbo.sp_send_dbmail

                             @recipients= @ToEmail,

                             @copy_recipients = @CCEmail,

                             @subject =  @subject1,

                             @body_format ='TEXT',

                             @body = @cmd

                  

          SET @errormsg = ''             

          SET @cmd = ''

          END

SET @pre_id = @id

fetch next FROM c4 INTO @id , @event , @computer , @operator , @source , @sourceid , @executionid , @starttime , @endtime , @message

END

 

close c4

deallocate c4

GO