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='claire.hsu@test123.com',@minute = 30
我们可以看到错误日志被成功发送到DBA。存储过程是我从网上找的,大家可以根据自己的需要做调整,如果觉得Text格式不好看可以转换为HTML格式。