How to use a variable to look up in a foreach loop container -ssis?

Question

I need to process all the files in a folder. something like this:

foreach loop over n

fileprocess.exe -argument_n filename_n

each argument is file specific and will be retrieved from a table. Need to design ssis package to batch process the files. foreach loop seems perfect for it.

I'm thinking of using lookup transform to retrieve the corresponding argument. My question are

• how to feed the variable @[user::filename] to the lookup transform?
• how to assign the lookup output into @[user::argument]
• Wonder if lookup transform is the right one to use?

Thanks a lot!

Answer 1

Assuming that you have a table containing columns for file name and corresponding argument, one way to implement your requirement is as below:

1. Add components from below figure to the Control Flow.
2. In the Foreach Loop, Enumerator is set to Foreach File Enumerator. The files are read from a folder, but you could use any type of enumerator.
3. Create 2 variables in the scope of the Loop to hold the file name and arguments, say, fname and farg. In the Collections tab of Foreach Loop Editor, assign index 0 to the variable fname.
4. In the Execute SQL task, write a query to retrieve the arguments for a given filename. Assign variable fname as input parameter, set Result Set to Single Row, and assign the result to variable farg.
5. In the Execute Process task, pass the variables fname and farg as arguments for your executable.