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:

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

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\";"

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?

what version of excel is it? And which ACE Driver do you have installed? and did you do this ? Project - Properties - Config Properties - Debugging and set Run64bitRunTime to FALSE. — SqlKindaGuy
– SqlKindaGuy, Commented Jan 17, 2018 at 11:50
@plaidDK Excel 2007. Tried setting 64-bit to false - same error. Not sure about ACE Driver but the Excel does import when I use a non-dynamic connection (i.e. without variable). Could the spaces in the filename have anything to do with it? Do I need to wrap something with quotes? — Adam
– Adam, Commented Jan 17, 2018 at 12:06
How does your full connectionstring looks like? (with value from variable) — SqlKindaGuy
– SqlKindaGuy, Commented Jan 17, 2018 at 12:10
Mine looks like this: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\thoje\Desktop\FSD\Korrektion af bruttoavance 234374 perioden 05.01.2018-12.01.2018.xlsx;Extended Properties="Excel 12.0 XML;HDR=YES"; and works only if i set Run64BitTime to FALSE - And OFC remember to install MICROSOFT ACE DRIVER — SqlKindaGuy
– SqlKindaGuy, Commented Jan 17, 2018 at 12:13
I suppose when you use Microsoft.JET.OLEDB you selected Micrsoft Excel 97-2003 in your Source? You should use Microsoft Excel 2007-2010 - This is how it looks when you use 97-2003 Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\thoje\Desktop\FSD\Copy of Korrektion af bruttoavance 316999 31.08.2017.XLSX;Extended Properties="Excel 8.0;HDR=YES"; — SqlKindaGuy
– SqlKindaGuy, Commented Jan 17, 2018 at 12:15

Hadi · Accepted Answer · 2018-01-18 21:45:59Z

2

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

edited Jan 18, 2018 at 21:45

answered Jan 18, 2018 at 21:39

Hadi

37.5k13 gold badges73 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SSIS 2012 Excel Source with Dynamic ConnectionString from Variable

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related