SSIS Script Task抛出死锁错误，查询数据库发邮件功能失败如何解决

根因分析 #

你遇到的变量死锁错误本质是SSIS变量锁重复申请、权限匹配错误导致的冲突：

• 你同时在Script Task属性的ReadWriteVariables配置项中声明了User::EmailData变量，又在代码中手动调用LockForWrite申请该变量的写锁，SSIS运行时会自动为属性中配置的变量加锁，重复申请同个变量的写锁直接触发死锁判定
• 你仅需要读取User::EmailData的值填充表格，完全不需要申请写锁，多余的高权限锁请求会大幅提升锁冲突概率
• 手动申请的变量锁未显式释放，在Foreach循环迭代场景下会出现上一轮锁未释放、下一轮重复申请的冲突问题

修复步骤 #

方案一（推荐：使用SSIS自动变量管理，无需手动锁） #

1. 打开Script Task的属性配置面板，做以下修改：
   • 将User::EmailData、User::UserEml两个变量从ReadWriteVariables列表中移除，添加到ReadOnlyVariables列表中
2. 替换Script Task的Main方法代码为以下内容，删除所有手动锁相关逻辑：

public void Main()
{
    string User_Recepient_Email_ID = Dts.Variables["User::UserEml"].Value.ToString();
    var data = Dts.Variables["User::EmailData"].Value;

    OleDbDataAdapter da = new OleDbDataAdapter();
    DataTable dt = new DataTable();
    da.Fill(dt, data);

    SendMailMessage("loadJob@xyz.com", User_Recepient_Email_ID, "ETL Load Status Report", ConvertDataTableToHTML(dt), true, "smtp.xxxxxxxx.org");

    Dts.TaskResult = (int)ScriptResults.Success;
}

方案二（保留手动锁逻辑的修改方式） #

1. 打开Script Task的属性配置面板，清空ReadWriteVariables和ReadOnlyVariables列表中所有自定义变量配置，避免SSIS自动加锁
2. 修改Main方法中的锁逻辑，将写锁改为读锁，用完变量后立刻释放锁：

public void Main()
{
    Variables varCollection = null;
    try
    {
        // 仅申请需要的读锁，不要申请多余的写锁
        Dts.VariableDispenser.LockForRead("User::EmailData");
        Dts.VariableDispenser.LockForRead("User::UserEml");
        Dts.VariableDispenser.GetVariables(ref varCollection);
        
        string User_Recepient_Email_ID = varCollection["User::UserEml"].Value.ToString();
        var data = varCollection["User::EmailData"].Value;
        
        // 拿到变量值后立刻释放锁，不要占锁执行后续发邮件等慢操作
        varCollection.Unlock();

        OleDbDataAdapter da = new OleDbDataAdapter();
        DataTable dt = new DataTable();
        da.Fill(dt, data);

        SendMailMessage("loadJob@xyz.com", User_Recepient_Email_ID, "ETL Load Status Report", ConvertDataTableToHTML(dt), true, "smtp.xxxxxxxx.org");

        Dts.TaskResult = (int)ScriptResults.Success;
    }
    catch(Exception ex)
    {
        // 异常场景也要确保锁释放
        if(varCollection != null && varCollection.Locked)
        {
            varCollection.Unlock();
        }
        Dts.Events.FireError(0, "Script Task", ex.Message, string.Empty, 0);
        Dts.TaskResult = (int)ScriptResults.Failure;
    }
}

额外优化建议 #

你的SQL查询关联了4张表，若数据量较大，在Foreach循环中逐用户执行查询也可能引发数据库层面的锁冲突，可以做以下优化：

• 提前一次性查询出所有用户的订单数据，缓存到临时表或者对象变量中，再循环处理发送邮件，避免循环中重复查询数据库
• 确认关联字段Customer_ID、OrderId、Inventory_ID都有对应索引，降低查询执行时间，减少锁持有时间

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