ConnectionManagerType = Excel with dynamic connection string
Posted on 2012-03-26
System environment Windows 7 Pro, 64 bit OS
I want to import data in xls file into a table in my SSIS package. I created a ssis ConnectionManagerType = Excel (WC_Crosswalk) and gave a dynamic connection string to connect to the xls file. The connection string is copied below:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+ @[User::CrosswalkPath] +";Extended Properties=\"EXCEL 8.0;HDR=YES\";"
if the value of the CrosswalkPath = "S:\\Offload\\WC\\From Client\\Images\\WCNYE1.xls"
as copied from the watch window
I understand that the connection string would be:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=S:\Offload\\WC\From Client\Images\WCNYE1.xls;Extended Properties="EXCEL 8.0;HDR=YES";
When I run this package, my data flow task fails with the following error message:
[WellcareExcelsheet ] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "WC_Crosswalk" failed with error code 0xC00F9304. "
Where did I go wrong?