SSIS Forloop: assign value to variable name based on iteration number

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.

Hmm... well, what is truly the problem? I mean, take a step back. Is it an absolute requirement that you end up with, say, 20 variables named Var1 through Var20, filled with values from iterations 1 through 20? Or is the real requirement that you need to store results from X number of iterations of a for loop container, and be able to access them later? — Rick Petersen
– Rick Petersen, Commented Nov 26, 2012 at 18:37
Hi, the variables aren't really called var1...var20....I just typed them that way so that the question makes sense. What I really want to do is delete all of the foreign keys of a table so that we can truncate the table, and then recreate the foreign keys. Someone created a new foreign key, so the old SSIS package failed on the truncation step as there was 1 key not yet deleted. I would like to store all of the foregin key names, delete them, and recreate them regardless of whether someone renames, deletes or addes new foreign keys to the table. — BlackRacerBoy
– BlackRacerBoy, Commented Nov 26, 2012 at 19:08
So yes, the actual requirement is to store results from X number of iterations to be able to access them later....:-). — BlackRacerBoy
– BlackRacerBoy, Commented Nov 26, 2012 at 19:15
Well, the reason I was asking, was really to know whether you had to have them in variables. Here is a link to a blog post that talks about using Object type variables in SSIS, using as a StringArray. I think that will give you the ability to store X number of values, then iterate through and readd them. I am not 100% certain I understand your use case so I won't comment on that, but this should help with the technique for solving it: rad.pasfu.com/index.php?/archives/… — Rick Petersen
– Rick Petersen, Commented Nov 26, 2012 at 19:23

billinkc · Accepted Answer · 2012-11-26 21:37:27Z

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

Control flow 2008

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

My For Loop is configured as

For Loop Editor

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.

enter image description here

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)

Collectives™ on Stack Overflow

SSIS Forloop: assign value to variable name based on iteration number

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related