queation about foreach loop

All,

I’m new to SSIS and have a question about foreach loop container.

Basically, I have 4 data flows and each of them has a variable that need be pass on to the next step, each of the variables will call a same stored procedure.

What should I use here? I guess I can use foreach loop container with the stored procedure inside of it, is that right? I tried all day yesterday and cannot make it work.

The foreach loop container did not call the procedure at all. Could someone kindly tell me how to do it?

If you are calling the same sproc 4 times, each time with different parameters then yes, the ForEach loop can be of help here.

You will need an execute SQL Task inside your ForEach loop container. The ForEach loop container itself doesn't actually call the sproc as you seem to think it will as suggested by the last line of your post.

-Jamie

|||

Jamie,

Thanks for your reply, Your blog helped me a lot. Thank you so much for putting such helpful information out there.

Back to my question, IF the foreach container is not supposed to call the Execute SQL task, then what should make it work?

Below is what I did, I don't know what i'm missing. I just cannot make it work!!!

*******************

ForeachLoop Editor:

Collection Tab:Foreach fromVariable Enumerator is the Enumerator

In Enumerator configuration, I created a new variable (V_EF) with the same datatype as the variable from the data flows

Variable Mappings tab: I added the 4 variables (come from each data flow) and the variable index are all 0

Expressions Table: I didn’t anything here because the procedure doesn’t return any value, what it does is update tables

Execute SQL Task Editor:

General Tab: ConnnectionType is OLEDB

SqlsourceType is Direct input

SQL statement isEXEC myProcedureName ?

bypassPrepare is false

Parameter Mapping: I use the variable (V_EF) I defined in Enumerator configuration

Result Set Tab: nothing here because the procedure will not return anything back

Expressions Tab: Nothing here

Many thanks!!

Jessie

|||please........|||

Jessie,

Under what circumstances do you not want to execute the Execute SQL Task?

Once you have answered that question then hopefully you can achieve what you need using expressions on your Precedence Constraints. Darren and Allan have a great demo of this here: http://www.sqlis.com/default.aspx?306

-Jamie