none
SSIS OnVariableValueChanged Deadlock RRS feed

  • Question

  • Hi,

    I'm having trouble getting the OnVariableValueChanged event handler to do anything useful. I've managed to get it to deadlock, but nothing else.

    I have a simple package, with one "Execute SQL Task" which executes "SELECT 1 AS RC" and assigns the value to an SSIS variable "RowCount". I've set RaiseChangeEvent to true.

    I've also created an event handler at the package level for the OnVariableValueChanged event. In the eventhandler I have a Script Task, I select the variable "RowCount" as a readonly variable and in the script body I have...

    public void Main()
    {
    // TODO: Add your code here
                String s = Dts.Variables["User::RowCount"].Value.ToString();
    Dts.TaskResult = (int)ScriptResults.Success;
    }

    Very simple stuff. When I execute the package I get ...

    Error: 0xC001405B at Script Task: A deadlock was detected while trying to lock variable "User::RowCount" for read access. A lock could not be acquired after 16 attempts and timed out.
    Error: 0x2 at Script Task: A deadlock was detected while trying to lock variable "User::RowCount" for read access. A lock could not be acquired after 16 attempts and timed out.
    Task failed: Script Task

    Thanks in advance,

    Gary

    Monday, August 17, 2015 3:31 PM

Answers

  • So I figured out where I was going wrong. The OnVariableValueChanged event actually has closely coupled variables, not that its clear. System::VariableName and System::VariableValue. If I access these inside the event handler all is fine.

    public void Main()
    {
    // TODO: Add your code here
                String msgRowCount = String.Format("{0} : {1}", 
                    Dts.Variables["System::VariableName"].Value.ToString(), 
                    Dts.Variables["System::VariableValue"].Value.ToString()
                    );
                bool fireAgain = false;
                Dts.Events.FireInformation(3, "Process Values", msgRowCount, "", 0, ref fireAgain);
    Dts.TaskResult = (int)ScriptResults.Success;
    }

    Wednesday, August 19, 2015 8:16 AM

All replies

  • Hi Gary,

    I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated.

    Thank you for your understanding and support.

    Regards,
    Katherine Xiong


    Katherine Xiong
    TechNet Community Support

    Tuesday, August 18, 2015 5:49 AM
    Moderator
  • So I figured out where I was going wrong. The OnVariableValueChanged event actually has closely coupled variables, not that its clear. System::VariableName and System::VariableValue. If I access these inside the event handler all is fine.

    public void Main()
    {
    // TODO: Add your code here
                String msgRowCount = String.Format("{0} : {1}", 
                    Dts.Variables["System::VariableName"].Value.ToString(), 
                    Dts.Variables["System::VariableValue"].Value.ToString()
                    );
                bool fireAgain = false;
                Dts.Events.FireInformation(3, "Process Values", msgRowCount, "", 0, ref fireAgain);
    Dts.TaskResult = (int)ScriptResults.Success;
    }

    Wednesday, August 19, 2015 8:16 AM