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
Related Articles
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 ...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 ...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 ...Excel Source Plus: How to modify the source region dynamically at runtime
The source region is available in the data flow properties list. Follow these steps to set up an expression: Create a variable that can be loaded with the desired region. The variable should be a string and the order should be: start column, start ...DataFlowSetting .NET attribute used with Data Flow Task Plus
The DataFlowSetting .NET attribute defines a list of references to different component properties in the SSIS data flow. It defines the link between the initialization parameter and a specific SSIS data flow component parameter. You can apply one ...