SSIS 脚本任务抛出死锁错误 [英] SSIS Script Task is throwing deadlock error

问题描述

我正在尝试查询数据库并发送电子邮件正文中的内容.我尝试遵循

运行包时出现错误

<块引用>

错误:脚本任务中的 0xC001405C:尝试锁定变量User::EmailData"时检测到死锁用于读/写访问.尝试 16 次后无法获取锁.锁定超时.`

错误:脚本任务中的 0xC001405D:尝试锁定变量System::InteractiveMode"时检测到死锁用于读取访问和变量User::EmailData"用于读/写访问.尝试 16 次后无法获取锁.锁定超时.

错误:脚本任务中的 0x1:尝试锁定变量User::EmailData"时检测到死锁用于读/写访问.尝试 16 次后无法获取锁.锁超时.任务失败:脚本任务

警告:每个用户在 Foreach 循环中出现 0x80019002:SSIS 警告代码 DTS_W_MAXIMUMERRORCOUNTREACHED.Execution 方法成功，但引发的错误数 (5) 达到了允许的最大值 (1)；导致失败.当错误数量达到 MaximumErrorCount 中指定的数量时，就会发生这种情况.更改 MaximumErrorCount 或修复错误.警告:0x80019002 在 SurplusMouse_EmailOrderDetail:SSIS 警告代码 DTS_W_MAXIMUMERRORCOUNTREACHED.Execution 方法成功，但引发的错误数 (5) 达到了允许的最大值 (1)；导致失败.当错误数量达到 MaximumErrorCount 中指定的数量时，就会发生这种情况.更改 MaximumErrorCount 或修复错误.SSIS包

编辑

下面是查询

SELECT cus.CustomerNumber 作为 CustomerNumber，cus.Location 作为 ReceivingLocation，i.StrainName 作为 StrainName，i.StrainCode 作为 StrainCode，i.Age 作为年龄，i.Sex 作为性别，i.Genotype 作为基因型，i.RoomNumber 作为 SentFrom，io.OrderQuantity 作为 OrderQuantityFROM [dbo].[MouseOrder] mo加入 [dbo].[Customer] cus 上 cus.Customer_ID = mo.CustomerId在 io.OrderId = mo.MouseOrder_ID 上加入 [dbo].[InventoryOrder] ioJOIN [dbo].[Inventory] ​​i on i.Inventory_ID = io.InventoryIdWHERE mo.OrderDate = convert(date,getdate() AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time') 和 mo.SAMAccountEmail = ?

解决方案

处理你得到的东西，错误正在从你与变量交互的方式中蔓延.您提供的代码是他在 2005 时代必须做的事情，尽管那时它已经是 VB.NET，因为 C# 不是一个选项.您应该能够通过 Dts.Variables 集合直接访问变量，然后获取它们的 Value - 根据需要转换为字符串或保留为对象类型.

 string User_Recepient_Email_ID = Dts.Variables[User::UserEml"].Value.ToString();var data = Dts.Variables[User::EmailData"].Value;OleDbDataAdapter da = new OleDbDataAdapter();数据表 dt = 新数据表();da.Fill(dt, 数据);

看起来您实际上并未写入值，因此我将在第一个屏幕截图中将它们从 ReadWrite 更改为 ReadOnly.

I am trying to query the database and send the content in the body of the email. I tried following here for my use case. But when I try to run the package it throws deadlock error. Can anyone please suggest what is that I am missing

Script is like below

namespace ST_ac39a1a4cb6047819cc46d683db46ac6
{
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        }

        public void Main()
        {
            Variables varCollection = null;

            string User_Recepient_Email_ID = Dts.Variables["User::UserEml"].Value.ToString();

            Dts.VariableDispenser.LockForWrite("User::EmailData");
            Dts.VariableDispenser.GetVariables(ref varCollection);
            var data = varCollection["User::EmailData"].Value;

            OleDbDataAdapter da = new OleDbDataAdapter();
            DataTable dt = new DataTable();
            da.Fill(dt, varCollection["User::EmailData"].Value);

            SendMailMessage("[e-mail]", User_Recepient_Email_ID, "ETL Load Status Report", ConvertDataTableToHTML(dt), true, "smtp.xxxxxxxx.org");

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


        public static string ConvertDataTableToHTML(DataTable dt)
        {
            string html = "<table border ='1'>";
            //add header row
            html += "<tr>";
            for (int i = 0; i < dt.Columns.Count; i++)
                html += "<th>" + dt.Columns[i].ColumnName + "</th>";
            html += "</tr>";
            //add rows
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                html += "<tr style='color:blue;'>";
                for (int j = 0; j < dt.Columns.Count; j++)
                    html += "<td>" + dt.Rows[i][j].ToString() + "</td>";
                html += "</tr>";
            }
            html += "</table>";
            return html;
        }
        private void SendMailMessage(string From, string SendTo, string Subject, string Body, bool IsBodyHtml, string Server)
        {
            MailMessage htmlMessage;
            SmtpClient mySmtpClient;

            htmlMessage = new MailMessage(From, SendTo, Subject, Body);
            htmlMessage.IsBodyHtml = IsBodyHtml;

            mySmtpClient = new SmtpClient(Server);
            mySmtpClient.Credentials = CredentialCache.DefaultNetworkCredentials;
            mySmtpClient.Send(htmlMessage);
        }   
    }
}

And the Task Script property

When running the package the errors are

Error: 0xC001405C at Script Task: A deadlock was detected while trying to lock variables "User::EmailData" for read/write access. A lock cannot be acquired after 16 attempts. The locks timed out.`

Error: 0xC001405D at Script Task: A deadlock was detected while trying to lock variables "System::InteractiveMode" for read access and variables "User::EmailData" for read/write access. A lock cannot be acquired after 16 attempts. The locks timed out.

Error: 0x1 at Script Task: A deadlock was detected while trying to lock variables "User::EmailData" for read/write access. A lock cannot be acquired after 16 attempts. The locks timed out. Task failed: Script Task

Warning: 0x80019002 at Foreach Loop each User: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (5) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. Warning: 0x80019002 at SurplusMouse_EmailOrderDetail: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (5) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. SSIS package

EDIT

Below is the query

SELECT cus.CustomerNumber as CustomerNumber,cus.Location as ReceivingLocation, 
       i.StrainName as StrainName,i.StrainCode as StrainCode,i.Age as Age,
       i.Sex as Sex,i.Genotype as Genotype,i.RoomNumber as SentFrom,io.OrderQuantity as OrderQuantity
FROM [dbo].[MouseOrder] mo
JOIN [dbo].[Customer] cus on cus.Customer_ID = mo.CustomerId
JOIN [dbo].[InventoryOrder] io on io.OrderId = mo.MouseOrder_ID
JOIN [dbo].[Inventory] i on i.Inventory_ID =  io.InventoryId 
WHERE mo.OrderDate = convert(date,getdate() AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time') and mo.SAMAccountEmail = ?

解决方案

Working with what you got, the error is creeping in from the way you're interacting with Variables. The code you supplied is how he had to do things back in the 2005 era, although it'd have been VB.NET at that point as C# wasn't an option. You ought to be able to directly access the variables through the Dts.Variables collection and then get their Value - casting to string or leaving as object type as needed.

        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);

It doesn't look like you're actually writing to the values so I would change them from ReadWrite to ReadOnly in the first screenshot.

这篇关于SSIS 脚本任务抛出死锁错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！