Unlock Variable in SSIS

Question

I have already ask this question and I thought I found a solution but I am stuck yet again. I am having issues with unlock variables.

I'm running a SQL Server Agent Job which has a script task with Microsoft Visual C# 2008 which is below. I haven't listed the variables in Script task editor read/write variable list. In my SSIS package I have For Loop Container which loop over this script task until the file does exist.

https://mitchellsql.wordpress.com/2014/09/06/ssis-script-taskdoes-file-exists/

  public void Main()
    {

        // Lock variables
        Dts.VariableDispenser.LockForRead("User::FolderPath");
        Dts.VariableDispenser.LockForWrite("User::FileExistsFlg");

        // Create a variables 'container' to store variables
        Variables vars = null;

        // Add variables from the VariableDispenser to the variables 'container'
        Dts.VariableDispenser.GetVariables(ref vars);

        string filepath;
        filepath = vars["User::FolderPath"].Value.ToString();
        vars["User::FileExistsFlg"].Value = File.Exists(filepath);

        // Release the locks
        vars.Unlock();

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

Error

I get this error when the SQL Job fails but the package runs perfectly when run via Visual Studio:

Message

Executed as user: PSFACAMDWHSQL1\SYSTEM. Microsoft (R) SQL Server Execute Package Utility Version 10.50.4270.0 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 12:26:43 Error: 2015-03-03 12:41:25.79 Code: 0xC001404F
Source: For Loop Container Description: This Variables collection has already been unlocked. The Unlock method is called only once on a dispensed Variables collection. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 12:26:43 Finished: 12:41:25 Elapsed: 881.921 seconds. The package execution failed. The step failed.

I would appreciate any help.

Be certain you are not getting confused with variable scope. You can have two identically named variables in a different scope and it makes things confusing — Nick.Mc, Mar 05 '15 at 11:21
In this example, https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.variabledispenser.getvariables.aspx the two variables are first `LockForRead` _before_ `GetVariables` is called. This is different to your code. Try using `LockForWrite` before you call `GetVariables` — Nick.Mc, Mar 05 '15 at 12:52
@Nick Only one variable is LockforRead and the second is LockForWrite both are not LockforRead. Not sure if I misunderstood. — Djbril, Mar 05 '15 at 13:12
Lockforwhatever... your code is different to the sample code in the link I posted. The link makes two other calls before `GetVariables` that you don't... look at the code in the link I posted and compare it to yours. — Nick.Mc, Mar 05 '15 at 14:03

score 0 · Answer 1 · answered Mar 03 '15 at 15:18

You probably listed the variable in the Script task editor read/write variable list ("User::FileExistsFlg") and then in your code you lock it again. Its one or the other. When I created an example listing a user variable twice my SSIS package hung - I probably did not wait long enough for it to err out. This worked fine as long as I did not list the variable User::A1:

Dts.VariableDispenser.LockForWrite("User::a1");

        // Create a variables 'container' to store variables
        Variables vars = null;

        // Add variables from the VariableDispenser to the variables 'container'
        Dts.VariableDispenser.GetVariables(ref vars);

        //string filepath;
       // filepath = vars["User::FolderPath"].Value.ToString();
       // vars["User::FileExistsFlg"].Value = File.Exists(filepath);
        vars["User::a1"].Value = 35;
        MessageBox.Show(vars["User::a1"].Value.ToString());
        // Release the locks
        vars.Unlock();

        Dts.TaskResult = (int)ScriptResults.Success;

I didn't list the variable in the script task editor. – Djbril Mar 03 '15 at 15:29 — Djbril, Mar 03 '15 at 15:29

Unlock Variable in SSIS

1 Answers1