SQL Agent停止包时触发审计日志写入的实现方案咨询

核心结论 #

SQL Agent手动停止SSIS包时，默认不会触发常规的包结束事件，但有几种靠谱的方式能实现enddatetime和success位的写入：

可行方案 #

1. 利用包的错误/失败事件处理 #

正常执行时，OnPostExecute事件会自动触发，写入enddatetime和success=1。当包被SQL Agent强制停止时，SSIS会触发OnError或OnTaskFailed事件，你可以在这些事件的处理程序里调用审计存储过程，设置enddatetime=GETDATE()、success=0。

• 关键点：把事件处理程序的传播级别设为“FailParentOnFailure”，防止事件被上层逻辑吞掉。

2. 优化OnQueryCancel的实现 #

如果必须用OnQueryCancel，别在事件里反复调用存储过程，而是先设个布尔变量（比如@IsCancelRequested），只在首次检测到取消请求时执行一次审计写入：

// OnQueryCancel事件中的脚本任务逻辑
bool isCanceled = (bool)Dts.Variables["User::IsCancelRequested"].Value;
if (!isCanceled)
{
    if (Dts.Events.QueryCancel())
    {
        Dts.Variables["User::IsCancelRequested"].Value = true;
        // 调用审计存储过程
        using (var conn = new SqlConnection(Dts.Variables["User::ConnString"].Value.ToString()))
        {
            var cmd = new SqlCommand("dbo.UpdateAuditLog", conn)
            {
                CommandType = CommandType.StoredProcedure
            };
            cmd.Parameters.AddWithValue("@AuditId", Dts.Variables["User::AuditId"].Value);
            cmd.Parameters.AddWithValue("@EndDateTime", DateTime.Now);
            cmd.Parameters.AddWithValue("@Success", 0);
            conn.Open();
            cmd.ExecuteNonQuery();
        }
    }
}
Dts.TaskResult = (int)ScriptResults.Success;

• 这种方式只会在取消请求首次出现时执行写入，不会因为反复检查拖慢包的运行速度。

3. 从SQL Agent作业层面补全 #

给作业加个后置T-SQL步骤，检查执行SSIS包的步骤状态，如果是取消或失败，就更新对应的审计记录：

DECLARE @JobId UNIQUEIDENTIFIER = (SELECT job_id FROM msdb.dbo.sysjobs WHERE name = '你的作业名');
DECLARE @StepId INT = 1; -- 对应执行SSIS包的步骤ID
DECLARE @AuditId INT = (SELECT MAX(AuditId) FROM dbo.AuditLog WHERE StartDateTime >= DATEADD(MINUTE, -30, GETDATE())); -- 按需调整审计ID的获取逻辑

UPDATE dbo.AuditLog
SET EndDateTime = GETDATE(), Success = 0
WHERE AuditId = @AuditId
AND EXISTS (
    SELECT 1 FROM msdb.dbo.sysjobsteps
    WHERE job_id = @JobId
    AND step_id = @StepId
    AND last_run_outcome IN (0, 3) -- 0=失败，3=取消
);

• 这个方案属于兜底手段，即使包层面的事件没触发，也能通过作业步骤补全审计。

踩过的坑说明 #

• OnVariableValueChanged为什么没用？因为SQL Agent停止包时不会修改包内的变量，所以这个事件根本不会被触发。
• 别指望OnTerminate事件：它只在包正常终止或者通过代码调用Dts.Terminate()时触发，SQL Agent的强制停止不会触发它。

内容的提问来源于stack exchange，提问作者Rusty_Gates