How to pass the excel connection string dynamically in the foreach loop container for Excel Source?

Question

In the Foreach Loop Container, I have the variable set to the Expressions / Directory.

In the Excel Source, I have mapped the Excel Connection String with the same variable mentioned in the Foreach loop Container.

However, I am getting an error

This error is thrown by Connections collection when the specific connection element is not found

You want to use the ExcelFilePath property not ConnectionString when wanting to dynamically change the Excel Connection via an expression: https://stackoverflow.com/questions/52962691/how-to-set-file-source-via-parameter-in-ssis/52970144#52970144 — Tim Mylott, Jan 08 '19 at 19:59
Thank you Tim, In my case that folder path which you see it on the answer screen shot is picked from a variable. Folder path is set in a variable — goofyui, Jan 08 '19 at 20:03
How to fetch the Excel File Path from a variable ? which is in the Foreach Loop Container — goofyui, Jan 08 '19 at 20:09
I think I understand. Don't use the same variable you are using for the Foreach Loop. I assume your User::InboundFilePath is where all the excel files are located you want to process. Create a second variable and then in the foreach loop make sure "Retrieve file name" is set to Fully Qualified, under Variable Mappings select that second variable you created with index 0. Now use that new variable on the ExcelFilePath property via an expression. The foreach loop works through each of the files it assigns the full path and name to the new variable, which is then passed to the excel connection. — Tim Mylott, Jan 08 '19 at 20:17
@goofyui if you are looking for a detailed step-by-step article covering this issue, check the hyperlink that i provided in my answer. — Hadi, Jan 08 '19 at 20:36
@goofyui i added more links check out, new links contains screenshots that can help more — Hadi, Jan 08 '19 at 21:03

Hadi · Accepted Answer · 2019-01-08T21:02:34.297

2

You can perform this in 3 steps:

In the for each loop container, go to Variable Mappings and Map the result of the loop into a variable.
On the Excel Connection Manager, Go to expressions, Set the value of ExcelFilePath property expression to the variable you created in the for each loop.
On the dataflow task Change the Delay Validation property value to True.

For a detailed step-by-step tutorials, check the following links:

edited Jan 08 '19 at 21:02

answered Jan 08 '19 at 20:30

Hadi

I am facing an error on Expression Evaluation .. Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + @[User::TestFilePath] + ";Extended Properties="EXCEL 12.0 XML;HDR=YES"; – goofyui Jan 08 '19 at 21:35
1

Don't assign expression to `ConnectionString` property, assign it to `ExcelFilePath property` – Hadi Jan 08 '19 at 21:37
1

Or write it as following: `"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + @[User::TestFilePath] + ";Extended Properties=""EXCEL 12.0 XML;HDR=YES"";"` ... you missed to start with a double quotation – Hadi Jan 08 '19 at 21:38
Thank you.. but problem with ExcelFilePath Property is, we need to provide the actual path of the excel file. Once the package is developed in Dev environment, we will be moving to different environments to run the package from the Job Scheduler. So it is better to have it in the connection string property where we can achieve this dynamically – goofyui Jan 08 '19 at 21:46
This is why i mentioned that you have to set the `Delay Validation` property to `True` – Hadi Jan 08 '19 at 21:48

1 Answers1