SSIS Forloop: assign value to variable name based

Question

I am trying to use a ForLoop container to assign values to up to 10 different variables....

var1 var2 . . . var10

based on the iteration number.

I need the loop to iterate a certain number of times based on a variable that has been assigned an integer value based on a result set from a query...that part is working just fine.

I am at a loss as how to tell the forloop which variable to use to assign the value....ie if I'm at itereation #1 then assign value to var1, iteration #2 then assign value to var2....and so on.

Any guidance would be much appreciated.

Answer 1

While I'm not 100% sure on why you'd want this approach, to answer the question, you could go 2 routes

In a 2005-2008R2 environment, your control flow would probably look something like this

I've defined 11 variables, all of type Int32. Counter and then Variable1 through Variable10.

My For Loop is configured as

The assignment of the current value to a variable will be handled in a Script Task. I have set the Counter variable as a read only variable, and Variable1-10 are read writes. I then have a basic Switch statement in there to handle the mapping

    public void Main()
    {
        int counter = (Int32) Dts.Variables["User::Counter"].Value;

        switch (counter)
        {
            case 1:
                Dts.Variables["User::Variable1"].Value = counter;
                break;
            case 2:
                Dts.Variables["User::Variable2"].Value = counter;
                break;
            case 3:
                Dts.Variables["User::Variable3"].Value = counter;
                break;
            case 4:
                Dts.Variables["User::Variable4"].Value = counter;
                break;
            case 5:
                Dts.Variables["User::Variable5"].Value = counter;
                break;
            case 6:
                Dts.Variables["User::Variable6"].Value = counter;
                break;
            case 7:
                Dts.Variables["User::Variable7"].Value = counter;
                break;
            case 8:
                Dts.Variables["User::Variable8"].Value = counter;
                break;
            case 9:
                Dts.Variables["User::Variable9"].Value = counter;
                break;
            case 10:
                Dts.Variables["User::Variable10"].Value = counter;
                break;
            default:
                break;
        }
        Dts.TaskResult = (int)ScriptResults.Success;
    }

The 2012 release of SQL Server provides an out of the box Expression Task. If you prefer this approach and are not on 2012, the samples project had an Expression Task you could build and install on your server.

At any rate, you make use of precedence constraints of Expression and Constraint and then test for your value in there.

Even if I had a 2012 installation, I'd probably still go with the code route as I think it's a cleaner approach. Both approaches though leave you with hard coded mappings of value to VariableN. @STLRick's (Missouri represent!) thought of storing a complex variable in a variable of type Object would probably be a preferable approach although as I admitted I don't quite grok why you'd want to store off the values of a For Loop as you'd already know your ranges (even if based on dynamic initial and terminal values)