CODEWITHSUNDEEP
sql-serverssissql-server-2012ssis-2012
Neil P
Neil P

Reputation: 3210

Writing to a SSIS variable from inside a foreach loop

I have a script task inside a foreach loop that should write to a variable.

Dts.Variables["Variable"].Value =  "Hello";

Looks fine to me, however the variable doesn't ever seem to be written to. The variable name is spelt correctly and is set as ReadWrite in the script task configuration.

The variable has package scope, is there something related to it being in a loop that could affect it? I've also tried using Dts.VariableDispencer.

Update: I have an int that I also write to alongside that does get written to fine. Aside from the different data type, the method is identical. Any ideas why I would fail to write a string?

Upvotes: 0

Views: 2545

Answers (2)

Neil P
Neil P

Reputation: 3210

It turns out the variable was actually set as an expression (totally forgot I had done that!). This was what was stopping it from being overwritten. Changing it to be a normal variable solved the issue.

Upvotes: 0

chridam
chridam

Reputation: 103475

Using this tutorial, you could try testing the variable by creating two methods: one for reading a variable and the other for writing to a variable. You must lock the variable first before trying to access it. Think of it as SSIS’s version of row locking. You want to make sure that you are getting the most accurate version of the variable at that point in time. What good would it do you, since things can run in parallel, if another task is updating the variable at the same time you are trying to read it? 

public void Main()
{
    string myVar = ReadVariable("myVar");
    MessageBox.Show(myVar, "myVar");
    WriteVariable("Variable", "Hello");
    string yourVar = ReadVariable("Variable");
    MessageBox.Show(yourVar, "yourVar");
    Dts.TaskResult = ScriptResults.Success;
}

private object ReadVariable(string varName)
{
    object rtnValue = null;
    //Create a variables collection to hold you object
    Variables var = default(Variables);

    try {
        //Lock the variable first to make sure that you have exclusive access
        //Think of it like a database object lock
        Dts.VariableDispenser.LockOneForRead(varName, var);

        //Now populate your result
        rtnValue = var(varName).Value;
    } catch (Exception ex) {
        throw ex;
    } finally {
        //You must make sure that you unlock the variable before exiting routine
        var.Unlock();
    }

    return rtnValue;
}

private void WriteVariable(string varName, object value)
{
    //Create a variables collection to hold you object
    Variables var = default(Variables);

    try {
        //Lock the variable first to make sure that you have exclusive access
        //Think of it like a database object lock
        Dts.VariableDispenser.LockOneForWrite(varName, var);

        //Now populate your result
        var(varName).Value = value;
    } catch (Exception ex) {
        throw ex;
    } finally {
        //You must make sure that you unlock the variable before exiting routine
        var.Unlock();
    }

}

Upvotes: 1

Related Questions