Dynamically assign filename to excel connection string

Question

This is my very first time playing with SSIS in SQL Server 2012. I can successfully read an excel file and load its content to a table in SQL server 2012. The task is a simple direct read excel file then copy to sql server with no validation or transformation for now. The task was successful. But when I tried to make the package read the file name from a variable instead of the original hard coded one, it was generating an error "DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D"

What I did was just replacing the hard coded connection string in the excel connection manager with an expression which took the value of a variable assigned by an expression

The variable was assigned the value before the data flow task started. The variable was checked and did have the correct value.

But the error below was generated when data flow task started.

It would be highly appreciated if someone could point out what I did incorrectly and advise me how to solve the issue.

Answer 1

Option A

The ConnectionString property for an Excel Connection Manager is not where I go to manipulate the current file, which is contrast to an ordinary Flat File Connection Manager.

Instead, put an expression on the Excel Connection Manager's ExcelFilePath property.

In theory, there should be no difference between ConnectionString and ExcelFilePath except that you will have more "stuff" to build out to get the connection string just right.

Also, be sure you're executing the package in 32 bit mode.

Option B

An alternative that you might be running into is that the design-time value for the Connection String isn't valid once it's running. When the package begins, it verifies that all of the expected resources are available and if they aren't, it fails fast rather than dieing mid load. You can delay this validation until such time as SSIS has to actually access the resource and you do this by setting the DelayValidation property to True. This property exists on everything in SSIS but I would start with setting it on the Excel Connection Manager first. If that still throws the Package Validation Error, try setting the Data Flow's delay validation to true as well.