How do I set up a dynamic file path in SSIS for an

Question

The name of the file changes based on months. Every month you have a new file.

I:\Test\Data_201303.xlsx

How do I set up a connection manager that will work with variable file paths?

Answer 1

Look for the "expression" property on the connection manager. That's where you set it to USER::VariableName.

More detail: if you "edit" the connection manager it will show you the edit window. That's not the one you want. Look at the "properties" window, which is where you will find the "Expressions" entry, in front of an empty box.

Click on the empty box, and it will show you a button with three dots on it. Click on that button. This pulls up the "Property Expression Editor". There is a dropdown with properties like "ConnectionString" on the left. On the right, there is ANOTHER button with three dots. Click on that button to pull up the "Expression Builder". If you have declared any variables, you will find them listed in the upper left hand corner, e.g., as User::VariableName, and you can then drag them into the Expression box, where they will appear as @[User::VariableName].

Not obvious, but doable.

Answer 2

You need to set the expression for the ServerName or ExcelFilePath property to modify ConnectionString of Excel connection manager dynamically using an SSIS package variable.

Here are some SO answers that deal with looping multiple Excel files :

How to loop through Excel files and load them into a database using SSIS package?

How to import Excel files with different names and same schema into database?