Problem using foreach loop container

Question

Post reply

Problem using foreach loop container

Bindu-330959

Old Hand

Points: 362
More actions
March 11, 2007 at 10:49 pm

#171619

We have a config table which has columns like pollingfolder, cop to folder etc.
Data looks like this
Config id polling folder
1 d:\dataload\acn
2 d:\dataload\ship
under ACN and Ship folders we can have more than 1 source files which had to be loaded into target
the load process has to be repeated for each file in all the folders like ACN and ship
I have used for each loop container to poll the folder.
Can we set the directory to be polled at runtime taking from config table.
For doing this I have used the property Directory in expression editor of for each loop. But it is not working.
If I hard code the variable value in variables section , then its working.
How to change the directory to be polled value at runtime?
March 14, 2007 at 8:00 am

This was removed by the editor as SPAM

Viewing 10 posts - 1 through 9 (of 9 total)

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

Me Da Man SSC Enthusiast Points: 188 More actions · Answer 1

This is probably too late to help you, but hopefully it will help someone else.

It is hard to tell why the expression isn't working with the details that you gave, but this is how I'd do it.

I would have a variable--lets call it folder_to_look_in. This variable is set by a package configuration. (I am assuming you know how to do this.) Then set up the expression to equal @[USER::folder_to_look_in] (I'm not 100% sure on the syntax, but you should be able to use the expression editor, then drag and drop the variable name). You may need to specify a (valid) value for the variable during design time or the loop won't validate.

I do something very similar to this for error reporting. If any of my tasks/packages fail, a number of log files are written to a folder, then I loop through this folder and attach all the files which get emailed to me (so if a load crashes it can interrupt my weekend). The folder is specified through an expression which refers to a variable set by a package configuration.

Make sense? If not, post your replies here.

Bindu-330959 Old Hand Points: 362 More actions · Answer 2

Thanks for your reply.

I did the samething but it doesnot work.

This would work if we have only one folder to be polled at each time.

But my requirement is we can multiple folders to be polled at each time. so have taken for loop container, which brings the foldername from a database table and inside for loop this am using for each loop container which actually poles the folder for the files.

Screenshots are attached for your reference.

Kindly put in you thoughts.

Me Da Man SSC Enthusiast Points: 188 More actions · Answer 3

I'll have to keep thinking about that one. The reason it doesn't work is that the expression that sets the folder doesn't get reevaluated every time the loop runs. I don't know how to force it to reevaluate. I did run into this problem before, but I can't remmeber how I got around it. I think I ended up using a script component/task.

FYI, the pictures don't show up. If you want to post pictures you can't just post them from your local hard drive. You need to post them somewhere on the internet first, then link to them here. If you just point to the picture on your local hard drive, nobody else can see the pictures.

If you can, repost the pictures because that might help.

Me Da Man SSC Enthusiast Points: 188 More actions · Answer 4

I'm sorry I haven't reposted with an answer. I haven't forgot about the problem, I just haven't been able to find a solution.

You might want to try posting in the MSDN SSIS forum. Jamie Thompson frequents that forum and he is the SSIS KING!.

If you find the answer, don't forget to post it here!

Bindu-330959 Old Hand Points: 362 More actions · Answer 5

Thanks for the help !!!

I will surely post the solution if i get any

chris corbin SSC Enthusiast Points: 172 More actions · Answer 6

Are you still seeking an answer to this question? if so send me an email and we can communicate more directly on this problem.

-Chris

sss aaaa Grasshopper Points: 13 More actions · Answer 7

Hi to all!

I have the same problem. Do anyone knows the solution?

Thanks,

Sergio

ArkWare Mr or Mrs. 500 Points: 525 More actions · Answer 8

The answer would be Nested foreach loops.

Build a foreach loop for a single folder in a variable, with the data transformations, then place that inside another foreach loop that loops through the folder list and updates the variable.

ArkWare