Increment a variable withing Foreach Loop - SSIS

Question

I have a package i am using the foreach loop to loop through the databases. I am passing a string and it loops though all the databases. Everything is perfect till here.

What i want to achieve is that for each databases it loops, it should increment a variable by 1. suppose if i have to loop through a total of 5 databases. And a package level variable(myvariable =24) is declared as 24. for each databases it loops it should increment the variable by 1.

For that i have created a script task inside the foreachloop container. ReadWriteVariables as myvariable. In the script editor. I have the following code

public void Main()
        {
     int varbl = Convert.ToInt32(Dts.Variables["User::myvariable"].Value.ToString());
     varbl++
       }

and then i am passing that incremented value to a storedprocedure. But its not incrementing. It is still taking the default value of 24. Any ideas how can i increment a variable in foreachloop container?

Answer 1

You have mentioned you have a package level variable, so the scope is OK. What you are missing is assigning the value back to the variable. As when you assign it's value into the varb1, then you have a copy of the original value in the varb1 (it is a structure and not class).

Second thing, if your variable is of type integer, then you do not need to convert the value to string and consequently to int. You can simply cast it as int.

So your code should look like the one below.

public void Main()
{
    Dts.Variables["User::myvariable"].Value = (int)Dts.Variables["User::myvariable"].Value + 1    
    Dts.TaskResult = (int)ScriptResults.Success;
}

Answer 2

0

@palum The behavior is perfectly normal. varb1 is losing it scope. There are quite a few solutions to that, but following you (In case myvariable value should be preserved), you should make a variable of a higher scope like package level OR lower depending upon your requirement. Increment that variable and pass it to your procedure.

answered Feb 10 '12 at 06:09 AM

Usman Butt
13.8k ● 6 ● 8 ● 14

(comments are locked)

Increment a variable withing Foreach Loop - SSIS

Follow this question

Related Questions