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+.
Book a Presales Call with us now and get 5% off SSIS+.
Related Articles
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 ...Excel Source Plus: How to modify the worksheet name dynamically at runtime
The source worksheet name 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 worksheet name. Right-click on the data flow canvas and select ...How can I set the Date Format for dates in the Excel Destination?
Scenario: You have a package with an OLEDB Source and an Excel + Destination. In the source table you have a field that is timestamp [DT_DBTIMESTAMP] column which is getting converted incorrectly to a number like 43951.5869164352 when loaded to ...Dynamic Data Flow - "... is DT_NTEXT, which is not supported with ANSI files" when trying to process input Unicode flat file
Trying to process input Unicode flat file fails with weird error: The data type for "Flat File Source.Outputs[Flat File Source Error Output].Columns[Flat File Source Error Output Column]" is DT_NTEXT, which is not supported with ANSI files. Use ...