SSIS 2012 Excel Source with Dynamic ConnectionString from Variable

Question

I have a simple SSIS 2012 package with:

• A Script Task obtains the latest .xlsx filename from a folder and stores the full path in a package-scoped variable: SourceFileExcel
• An Excel Connection Manager called FUNB to connect to the file
• An Excel Source using the FUNB Connection Manager

When trying to assign the ConnectionString dynamically, I get error:

[Excel Source [2]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "FUNB" failed with error code 0xC0209303. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

I have tried the following:

1. Add the Connection Manager by browsing to the actual file. Connect the Excel Source to it. Preview Data.

2. Once file loaded manually, update connection manager properties. Set ConnectionString Expression:

   "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+@[User::SourceFileExcel]+";Extended Properties=\"EXCEL 8.0;HDR=Yes; IMEX=1\";"
   

3. Above produces the error. Change the expression to ExcelFilePath set to:

   @[User::SourceFileExcel]
   

Both the ExcelFilePath and the ConnectionString expressions produce the same error. I have tried both with DelayValidation=True on the Excel Connection Manager and the Data Flow object.

I have another package on the same server that works in much the same way but is using a CSV connection. This package runs normally. The Windows Security Permissions on both source file folders is the same.

Any ideas on what I am doing wrong?

Answer 1

I think that you should use Microsoft.ACE.OLEDB.12.0 as Provider when connecting to excel 2007 or higher, Also make sure you are not running the package in 64-bit mode

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + @[User::SourceFileExcel] + ";Extended Properties=\"Excel 12.0;HDR=YES\";"

Or try the following format (without writing Extended Properties)

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + @[User::SourceFileExcel] + ";Excel 12.0;HDR=YES;IMEX=1"

If none of these suggestions works, then i think that this is not an expression issue, and you are facing the same issue in this link.

The problem maybe that a 64-bit office installation was found on the machine and it needed to install 32-bit AccessDatabaseEngine