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

qualifying table variables

hi all!

I am using table variables instead of creating a temp table because it seems to be faster

But now I need qualify the table variable so I can join it with another table having a field with same name of a field from the table variable. U know if I can do that?

ex: with temp table

create table #tmp... (F1...)
#tmp.f1

with table variable

declare @.temp table(...
@.table.f1 - can´t do it

the first question is if I can join the table variable with another table and how to do that qualifying the variable table, that is, putting the name of the var temp with the field, because the other table has a field with same name

thank to all and happiness for all 2004Hi all

I think I found the solution

It is not possible to qualify the talble variable because it is not part of a persistent table, so I cant assing table variables,

I saw it in

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_de-dz_66w5.asp

it is the same as in sql 2000 BOL

thanks. so I have to use a temp table to qualify it if I want to join the table. My thinking is that I can join table variables

hope it is useful to u|||Hi all!

Thanks for having the time to read it

I was wrong and found the solution.

I can join and qualify a table var, only aliasing the table as in

insert into @.table... join temp.field...
from @.vartemp temp

--

here temp is the alias of @.vartemp, so I can use this alias instead of @.vartemp when, for example, there are two fields with same name in two tables, and one is @.vartemp

Now, once solved it, my problem is with UPDATETEXT. I am updating a text field and need to qualify the @.vartable in order to update it using a pointer to the text field. Since there are not any FROM clause as above, I dont know where to qualify the @.vartable (tried qualifying in DECLARE, but cant do it there)

UPDATETEXT @.vartemp.pointer - cant use @.vartemp, need an alias, but where to give the alias?

Thanks all!