How to dynamically set the ExcelFilePath when looping through different excel files in a data flow.

How to dynamically set the ExcelFilePath when looping through different excel files in a data flow.

Scenario:
I have many Excel files with a same format to convert to text files, and I have created a ForEach Loop to loop through each Excel file to have a data flow with an excel source to flat file.  
Question:
How can I dynamically set the connection to the Excel file each time through the ForEach Loop?
Answer:
You should create an expression to assign a variable with the full path name to the excel file (example: inputFilePath) to ExcelFilePath. Many users mistakenly assign the varialbe to the ConnectionString property, however ConnectionString property is built from ExcelFilePath property and several other properties and would not be the correct property to assign the variable.

Incorrect way:


Correct way:


You should also set RetainSameConnection property in your connection to false, so it uses the new path in the connection each iteration of the Foreach Loop, instead of retaining the same default connection.  See this KB article:

You also, want to be sure to give your variable an initial default file path.  It will be replaced dynamically at runtime, but you will get an error message ("excel file not found") if you leave the variable blank.


    Book a Presales Call


    Are you new to COZYROC and evaluating our solutions? 
    Book a Presales Call with us now and get 5% off SSIS+.