SSIS Job失败作业通知

作为DBA我们需要监控各个作业的正常运行，而且需要能够及时的了解作业失败的原因。 对于SSIS作业，我们可以通过下面的步骤建立Mail通知。

Step1:

启用SSIS Logging：可以帮助我们捕获SSIS失败的信息(Right click on the white pane)

Right click on the white pane

Step 2: 选择 Loggings

 

Step 3: 选择"SSIS log provider for SQL Server"

Step 4: 增加

Step 5: 确保复选框都选上

 

Step 6:创建SQL Server Connection

Step 7: 点击详细Tab.

Step 8: 选择下面的事件：

• OnError
• OnPostExecute
• OnTaskFailed

点解确定并保存。

下面运行一下Pakage确保Log已经被记录：

执行包并查询Log 记录：

use SSIS_Configgoselect id, event, source, starttime, endtime, message from sysssislog 

Step9: 测试失败状态：

 

use SSIS_Configgoselect id, event, source, starttime, endtime, message from sysssislog

这里我们可以看到失败的记录已经被捕获了：

Executing the query "execute test" failed with the following error: "Could not find stored procedure 'test'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

到现在为止我们可以看到日志信息已经被保存到数据库，下面我们会创建一个存储过程将这些错误信息发送给DBA。

USE SSIS_Configgo CREATE PROCEDURE usp_FailedSSIS_SendMail          @ToEmail varchar(1000) = '',          @CCEmail varchar(1000) = '',          @minute int = null ASSET 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 c4declare @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 = 0begin                     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 = ''          endset @pre_id = @idfetch next from c4 into @id , @event , @computer , @operator , @source , @sourceid , @executionid , @starttime , @endtime , @message end close c4deallocate c4GO

测试：

exec usp_FailedSSIS_SendMail @ToEmail='[e-mail]',@minute = 30

我们可以看到错误日志被成功发送到DBA。存储过程是我从网上找的，大家可以根据自己的需要做调整，如果觉得Text格式不好看可以转换为HTML格式。