代码之家 › 专栏 › 技术社区 › Gowtham Ramamoorthy

尝试锁定变量时检测到死锁。尝试16次后无法获得锁。锁超时

ssis-2012 ssis

Gowtham Ramamoorthy · 技术社区 · 7 年前

我正在尝试使用SSIS在电子邮件中发送一组查询结果。下面是流程的屏幕截图和步骤。

截图:

步骤: 1、使用SQL任务在“queryresult”对象变量中获取查询结果

3、在foreachloop容器内使用script task捕捉对象变量“query result”中的数据

下面是我从互联网上复制的脚本任务中的代码。

使用系统; 使用Microsoft。SqlServer。Dts。运行时间; 使用系统。窗户。形式;

末端区域

namespace ST_77e6b4ea18824b909adc5568475fcf5c
 {

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



{

    public void Main()
    {
        Variables varCollection = null;
        string header = string.Empty;
        string message = string.Empty;

        Dts.VariableDispenser.LockForWrite("User::ValidationEmailMessage");
        Dts.VariableDispenser.LockForRead("User::Accountnumber");
        Dts.VariableDispenser.LockForRead("User::ProjectRefID");
        Dts.VariableDispenser.GetVariables(ref varCollection);

        if (varCollection["User::ValidationEmailMessage"].Value == string.Empty)
        {
            header = "Below are the list of Invalid ProjecRefID and Accountnumbers that are not matching with our existing data:\n\n";
            header += string.Format("{0}\t{1}\t\t\t{2}\n", "ProjectRefID", "Accountnumber");
            varCollection["User::ValidationEmailMessage"].Value = header;
            varCollection.Unlock();
        }


        //Format the query result with tab delimiters
        message = string.Format("{0}\t{1}\t{2}",
                                    varCollection["User::ProjectRefID"].Value,
                                    varCollection["User::Accountnumber"].Value);

        varCollection["User::ValidationEmailMessage"].Value = varCollection["User::ValidationEmailMessage"].Value + message;
        varCollection.Unlock();
        Dts.TaskResult = (int)ScriptResults.Success;

    }


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


}

}

我试图解决这个错误,但不知怎的,我无法解决这个问题。如果有人知道如何解决,请告诉我。

1 回复 | 直到 7 年前

Chris Mack 6 年前

除非出于某种原因需要锁定功能,否则您应该能够简单地编写Main方法,如下所示:

public void Main()
{
    string header = string.Empty;
    string message = string.Empty;

    if (Dts.Variables["User::ValidationEmailMessage"].Value == string.Empty)
    {
        header = "Below are the list of Invalid ProjecRefID and Accountnumbers that are not matching with our existing data:\n\n";
        header += string.Format("{0}\t{1}\t\t\t{2}\n", "ProjectRefID", "Accountnumber");
        Dts.Variables["User::ValidationEmailMessage"].Value = header;
    }


    //Format the query result with tab delimiters
    message =
        string.Format("{0}\t{1}\t{2}",
            Dts.Variables["User::ProjectRefID"].Value,
            Dts.Variables["User::Accountnumber"].Value);

    Dts.Variables["User::ValidationEmailMessage"].Value = Dts.Variables["User::ValidationEmailMessage"].Value + message;

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

还有,用你的 string.Format 代码中,您指定了三个索引,{0},{1}和{2},但您只提供了两个参数,即。 , "ProjectRefID", "Accountnumber");