SSIS - Loop through files from a file path based on the value in the variable

experts,

i have requirement i'll loading multiple files in sql server table , archive files when loaded. however, challenge , file path should dynamic based on value of variable (say, @productname).

for example: if running package variable @productname="product", file path "\\....\src\product", in case foreachloop loop through files in folder, load them table , archive files "\\....\src\product\archive" folder.

similarly, if @productname="product_ncp", foreachloop container should loop through files in "\\....\src\product_ncp" folder, load them table , archive them ""\\....\src\product_ncp\archive" folder.

any suggestions? should able run package manually passing "@product" value, create archive folder if doesn't exist, load data , archive files.

if can afford config table flexible approach. have table values each product

in package have execute sql task retrive values passing product name. assign them local variables created in ssis , use variables map required properties using expression builder

in above case need map filepath variable directory property of foreachloop.

---

please mark answer if solved issue
please vote helpful if helps solve issue
visakh
----------------------------
my wiki user page
my msdn page
my personal blog
my facebook page

SQL Server  >  SQL Server Integration Services