I am having trouble in working with ForEach Loop in SSIS. If anybody
can give me some tips, it will be very appreciated.

What I am trying to do is to read files in a folder and put them into
different folder. I have set up the collections tab with
FileEnumerator option and folder name and then created a variable on
Variables Mapping tab. But, when I try to read the variable mapped
from File System Task in the loop, it complains that the variable is
empty. I checked and try to figure out what might be problem, but I
don't have any clue - I think I set it correctly. Is there any
undocumented, weird behavior with ForEach Loop that prevents from a
variable being populated with enumerator value?

c.j.

Hello CJ:

I have used the ForEach loop quite a bit and it works fine for me. I
typcially use it to iterate over flat files in a folder and use each one as a
DataSource in a data flow task. Your application does not sound too different.

So you have a Variable named File_Name, and you have a mapping in your
ForEach task to populate that variable with what? The full path and file
name? What is the Index of the Variable Mapping? Is it still 0?

Then there is a File System task inside the loop? The File Operation is set
to "Move File". The Source is set to a Variable, that of File_Name, and what
is the destination? Hard-coded?

Try this: Right-click on the File System task and add a BreakPoint on the
Pre-Execute point. Run the package, and when it hits the break, open the
Local window and examine the contents of the File_Name variable. It should
change each time through the loop.

That may give some insight into the inner workings.

Keep us posted.
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"CJ" wrote:

Hello CJ:

I have used the ForEach loop quite a bit and it works fine for me. I
typcially use it to iterate over flat files in a folder and use each one as a
DataSource in a data flow task. Your application does not sound too different.

So you have a Variable named File_Name, and you have a mapping in your
ForEach task to populate that variable with what? The full path and file
name? What is the Index of the Variable Mapping? Is it still 0?

Then there is a File System task inside the loop? The File Operation is set
to "Move File". The Source is set to a Variable, that of File_Name, and what
is the destination? Hard-coded?

Try this: Right-click on the File System task and add a BreakPoint on the
Pre-Execute point. Run the package, and when it hits the break, open the
Local window and examine the contents of the File_Name variable. It should
change each time through the loop.

That may give some insight into the inner workings.

Keep us posted.
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"CJ" wrote:

Hello CJ:

I have used the ForEach loop quite a bit and it works fine for me. I
typcially use it to iterate over flat files in a folder and use each one as a
DataSource in a data flow task. Your application does not sound too different.

So you have a Variable named File_Name, and you have a mapping in your
ForEach task to populate that variable with what? The full path and file
name? What is the Index of the Variable Mapping? Is it still 0?

Then there is a File System task inside the loop? The File Operation is set
to "Move File". The Source is set to a Variable, that of File_Name, and what
is the destination? Hard-coded?

Try this: Right-click on the File System task and add a BreakPoint on the
Pre-Execute point. Run the package, and when it hits the break, open the
Local window and examine the contents of the File_Name variable. It should
change each time through the loop.

That may give some insight into the inner workings.

Keep us posted.
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"CJ" wrote:

Hello CJ:

I have used the ForEach loop quite a bit and it works fine for me. I
typcially use it to iterate over flat files in a folder and use each one as a
DataSource in a data flow task. Your application does not sound too different.

So you have a Variable named File_Name, and you have a mapping in your
ForEach task to populate that variable with what? The full path and file
name? What is the Index of the Variable Mapping? Is it still 0?

Then there is a File System task inside the loop? The File Operation is set
to "Move File". The Source is set to a Variable, that of File_Name, and what
is the destination? Hard-coded?

Try this: Right-click on the File System task and add a BreakPoint on the
Pre-Execute point. Run the package, and when it hits the break, open the
Local window and examine the contents of the File_Name variable. It should
change each time through the loop.

That may give some insight into the inner workings.

Keep us posted.
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"CJ" wrote:

Hello CJ:

I have used the ForEach loop quite a bit and it works fine for me. I
typcially use it to iterate over flat files in a folder and use each one as a
DataSource in a data flow task. Your application does not sound too different.

So you have a Variable named File_Name, and you have a mapping in your
ForEach task to populate that variable with what? The full path and file
name? What is the Index of the Variable Mapping? Is it still 0?

Then there is a File System task inside the loop? The File Operation is set
to "Move File". The Source is set to a Variable, that of File_Name, and what
is the destination? Hard-coded?

Try this: Right-click on the File System task and add a BreakPoint on the
Pre-Execute point. Run the package, and when it hits the break, open the
Local window and examine the contents of the File_Name variable. It should
change each time through the loop.

That may give some insight into the inner workings.

Keep us posted.
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"CJ" wrote:

Hello CJ:

I have used the ForEach loop quite a bit and it works fine for me. I
typcially use it to iterate over flat files in a folder and use each one as a
DataSource in a data flow task. Your application does not sound too different.

So you have a Variable named File_Name, and you have a mapping in your
ForEach task to populate that variable with what? The full path and file
name? What is the Index of the Variable Mapping? Is it still 0?

Then there is a File System task inside the loop? The File Operation is set
to "Move File". The Source is set to a Variable, that of File_Name, and what
is the destination? Hard-coded?

Try this: Right-click on the File System task and add a BreakPoint on the
Pre-Execute point. Run the package, and when it hits the break, open the
Local window and examine the contents of the File_Name variable. It should
change each time through the loop.

That may give some insight into the inner workings.

Keep us posted.
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"CJ" wrote:

Hello CJ:

I have used the ForEach loop quite a bit and it works fine for me. I
typcially use it to iterate over flat files in a folder and use each one as a
DataSource in a data flow task. Your application does not sound too different.

So you have a Variable named File_Name, and you have a mapping in your
ForEach task to populate that variable with what? The full path and file
name? What is the Index of the Variable Mapping? Is it still 0?

Then there is a File System task inside the loop? The File Operation is set
to "Move File". The Source is set to a Variable, that of File_Name, and what
is the destination? Hard-coded?

Try this: Right-click on the File System task and add a BreakPoint on the
Pre-Execute point. Run the package, and when it hits the break, open the
Local window and examine the contents of the File_Name variable. It should
change each time through the loop.

That may give some insight into the inner workings.

Keep us posted.
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"CJ" wrote:

Hello CJ:

I have used the ForEach loop quite a bit and it works fine for me. I
typcially use it to iterate over flat files in a folder and use each one as a
DataSource in a data flow task. Your application does not sound too different.

So you have a Variable named File_Name, and you have a mapping in your
ForEach task to populate that variable with what? The full path and file
name? What is the Index of the Variable Mapping? Is it still 0?

Then there is a File System task inside the loop? The File Operation is set
to "Move File". The Source is set to a Variable, that of File_Name, and what
is the destination? Hard-coded?

Try this: Right-click on the File System task and add a BreakPoint on the
Pre-Execute point. Run the package, and when it hits the break, open the
Local window and examine the contents of the File_Name variable. It should
change each time through the loop.

That may give some insight into the inner workings.

Keep us posted.
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"CJ" wrote:

Thank you, Todd.

The cause turned out to be that I did not give meaningful initial
value for the variable. I was expecting the value would be replaced in
run time with correct file name and did not specify or just gave a
bogus value, but SSIS did not like it.

Your tips on utillizing BreakPoint in debug mode worked very well
after my several trials and errors. Now, I could finish the package,
with which I was trying to grab multiple csv files in the loop, parse
and put the data into database tables, and then move them into archive
folder.

Thanks.

c.j.