x

Increment a variable withing Foreach Loop - SSIS

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?
more ▼

asked Feb 10, 2012 at 12:41 AM in Default

palum gravatar image

palum
249 26 29 30

not an expert in SSIS but can't you do this with expressions instead of the script task?
Feb 10, 2012 at 01:50 AM Shawn_Melton
@Shawn_Melton, the For Loop Container has an AssignExpresion property where you can put expression for incrementing, however the Foreach Loop Container doesn't have such and you have to use a script task inside to be able to increment variables.
Feb 10, 2012 at 07:15 AM Pavel Pawlowski
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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;
}
more ▼

answered Feb 10, 2012 at 07:11 AM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

(comments are locked)
10|1200 characters needed characters left
@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.
more ▼

answered Feb 10, 2012 at 06:09 AM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x943

asked: Feb 10, 2012 at 12:41 AM

Seen: 2491 times

Last Updated: Feb 10, 2012 at 07:15 AM