sql server - SSIS dynamic connection to the source and destination. -

hi want create 1 ssis packge load excel file customer db.

i have 100 customers db's , have evrey new week 100 excel files (all db's , files have same structure) db's name customername , excel file name customername_date.

i want create job run once week ssis packge load excel files in folder customer db's , transfer file archive folder.

what best why dynamic connection source , destination.

this task can done using variables , expressions. difficult part create working connection string ole db connection.

1) processing each file in folder, use foreach loop.

• in loop edit window in collection tab, select "foreach file enumerator"
• select folder, filter out excel files (if needed) using wildcart , choose fully qualified name
• create variable storing file path , in tab variable mappings select variable. (in each loop, new file path saved in variable).

2) create new excel file connection based on 1 of excel files, right-click , in expressions, create expression connection string. in expression editor, set use yout file variable.

3) need create connection string ole db connection.

• create new variable holt it.
• create ole db connection , connect of databases.
• then right-click connection , backup conection string somewhere else, can use in future.

4) in foreach loop, create script task.

• in script task editor, choose readonlyvariables (your file name) , readwritevariables (your prepared connection string)
• click edit script , in new visualstudio window, create script.

• variables can read , written this:

  string myvalue = (string)dts.variables["user::"].value;

• you can parse excel name filename variable , create new connection string ole db match saved one.

5) edit ole db connection expressions , match connection string variable.

6) in foreach loop, create data flow task , double-click it. use excel source , make use excel connection. create ole db destination , make use ole db connection.

---

use answer suggestion, maybe need soma playing around before work. based on experience, approach should work.