Adding Variable to SSIS foreach loop

Question

Adding Variable to SSIS foreach loop

swathi9.chinni

SSChasing Mays

Points: 629
More actions
July 12, 2012 at 8:50 am

#272320

Hi all,
Im newbee to SQL Server.
Can anyone let me know how to assign a variable in for each loop container,when the situation is like this..
In my loop container there is one execute sql task tht updates the table and 1 dataflow task,in tht my Source is SQL table and Destination is Flatfile..
If I run the execute sql task n dataflow task out side the loop container it works but whn its in loop container it doesn’t coz I haven’t assigned variable to it.
To Assign a variable I do SSIS-->Variable-->Add variable--> from hear I don’t know how to do…
I understand for working foreach loop container i need to assign some value to the variable but i don't understand how to do that
Thanks in Advance 🙂
Cheers,
Swathi

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

sabotta SSC Eights! Points: 818 More actions · Answer 1

What error message are you getting when you run the package? And, why does the Execute SQL Task need to run inside a Foreach Loop container -- For example, are you using the Foreach Loop container to interate through a set of files that each contain a Transact-SQL statement?

subbu1 Hall of Fame Points: 3281 More actions · Answer 2

pls check below url

http://www.rad.pasfu.com/index.php?/archives/18-Foreach-Loop-based-on-Variable-SSIS.html

swathi9.chinni SSChasing Mays Points: 629 More actions · Answer 3

Hi ,

I will make you clear with my package.I am unable to understand how to design this package please help me out in designing ..i am attaching you my package structure and the code in side the package i think this help you clear to understand where i am...

sabotta SSC Eights! Points: 818 More actions · Answer 4

I think the above example for Foreach Loop based on a variable would work for your package. You'd need to replace the ActiveX Script ("Begin Loop") with a Script Task so you can read/write to the SSIS variables.

Another option to consider is using the For Loop container. You can use iRowCount.Value > 0 as the value for the EvalExpression property. Then you just need to set the values for the InitExpression and AssignExpression properties. You use the For Loop Editor to set these property values. You can use either literals or expressions to specify the values; expressions can include variables.

You'd add the Data Flow task to the For Loop container, and the other control flow tasks that are part of the loop (i.e. Select Records task) .

This For Loop Container topic in BOL give an overview of the component. This SQLIS article has some For Loop Container examples.