Hi, I’ve got a small problem with an excel connection manager. Below is the series of error messages I receive:
Error: 2008-05-14 08:59:21.17 Code: 0xC0202009 Source: WiReCAPSLoad Connection manager "Excel Connection Manager" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0×80040E21.An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0×80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".End ErrorError: 2008-05-14 08:59:21.18 Code: 0xC020801C Source: Data Flow Task 1 Source - Sheet1$ [1] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.End ErrorError: 2008-05-14 08:59:21.18 Code: 0xC0047017 Source: Data Flow Task 1 DTS.Pipeline Description: component "Source - Sheet1$" (1) failed validation and returnederror code 0xC020801C.End ErrorProgress: 2008-05-14 08:59:21.18 Source: Data Flow Task 1 Validating: 66% completeEnd ProgressError: 2008-05-14 08:59:21.18 Code: 0xC004700C Source: Data Flow Task 1 DTS.Pipeline Description: One or more component failed validation.End ErrorError: 2008-05-14 08:59:21.18 Code: 0xC0024107 Source: Data Flow Task 1 Description: There were errors during task validation.End ErrorDTExec: The package execution returned DTSER_FAILURE (1).
So from the message I’m assuming that somewhere in the package configuration its missing the path to be able to find the Excel spreadsheet in question. But when opening up the package configuration I can’t see anything immediately wrong. For the path of the spreadsheet location I’ve used the full UNC path instead of any mapped drive locations I’ve being guilty of using in the past. Below is the Excel Connection Manager section of my configuration file:
<Configuration ConfiguredType="Property" Path="\Package.Connections[Excel Connection Manager].Properties[ConnectionString]" ValueType="String"> <ConfiguredValue>Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\domain.co.uk\dfs\w\winningproject\CAPStoWIREloadTest\CAPSLoad1.xls;Extended Properties="EXCEL 8.0;HDR=YES";</ConfiguredValue> </Configuration> <Configuration ConfiguredType="Property" Path="\Package.Connections[Excel Connection Manager].Properties[Description]" ValueType="String"> <ConfiguredValue> </ConfiguredValue> </Configuration> <Configuration ConfiguredType="Property" Path="\Package.Connections[Excel Connection Manager].Properties[ExcelFilePath]" ValueType="String"> <ConfiguredValue>\\domain.co.uk\dfs\w\winningproject\CAPStoWIREloadTest\CAPSLoad1.xls</ConfiguredValue> </Configuration> <Configuration ConfiguredType="Property" Path="\Package.Connections[Excel Connection Manager].Properties[ExcelVersionNumber]" ValueType="Int32"> <ConfiguredValue>3</ConfiguredValue> </Configuration> <Configuration ConfiguredType="Property" Path="\Package.Connections[Excel Connection Manager].Properties[FirstRowHasColumnName]" ValueType="Boolean"> <ConfiguredValue>1</ConfiguredValue> </Configuration> <Configuration ConfiguredType="Property" Path="\Package.Connections[Excel Connection Manager].Properties[InitialCatalog]" ValueType="String"> <ConfiguredValue> </ConfiguredValue> </Configuration> <Configuration ConfiguredType="Property" Path="\Package.Connections[Excel Connection Manager].Properties[Name]" ValueType="String"> <ConfiguredValue>Excel Connection Manager</ConfiguredValue> </Configuration> <Configuration ConfiguredType="Property" Path="\Package.Connections[Excel Connection Manager].Properties[Password]" ValueType="String"> <ConfiguredValue> </ConfiguredValue> </Configuration> <Configuration ConfiguredType="Property" Path="\Package.Connections[Excel Connection Manager].Properties[ProtectionLevel]" ValueType="Int32"> <ConfiguredValue>1</ConfiguredValue> </Configuration> <Configuration ConfiguredType="Property" Path="\Package.Connections[Excel Connection Manager].Properties[RetainSameConnection]" ValueType="Boolean"> <ConfiguredValue>0</ConfiguredValue> </Configuration> <Configuration ConfiguredType="Property" Path="\Package.Connections[Excel Connection Manager].Properties[ServerName]" ValueType="String"> <ConfiguredValue>\\domain.co.uk\dfs\w\winningproject\CAPStoWIREloadTest\CAPSLoad1.xls</ConfiguredValue> </Configuration> <Configuration ConfiguredType="Property" Path="\Package.Connections[Excel Connection Manager].Properties[UserName]" ValueType="String"> <ConfiguredValue> </ConfiguredValue>
I’ve checked the rest of the configuration file and wherever something needs a username and password, they are entered correctly. I’ve also made sure that the account I’m logged onto our server and running the package under can access the excel file in question with no issues.
What is abit strange is that I have another package that runs in a similar way and also uses an Excel Connection Manager but from a different folder in the same area and that runs fine. I’ve tried to compare the 2 sets of connection managers to see if I can spot the difference but I’ve been unable to so far. Al that I can see that is different is the folder winningproject somtimes being in lowercase and then sometimes having capitals, but that doesn’t seem to make any difference.
I’m hoping a fresh pair of eyes will be able to spot something obvious that I’ve missed!! Below is the Excel Connection Manager section of the configuration file I have that works fine.:
<Configuration ConfiguredType="Property" Path="\Package.Connections[Excel Connection Manager].Properties[ConnectionString]" ValueType="String"> <ConfiguredValue>Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\domain.co.uk\dfs\w\WinningProject\ServiceRequestLoadTest\SRUpdate.xls;Extended Properties="Excel 8.0;HDR=YES";</ConfiguredValue> </Configuration> <Configuration ConfiguredType="Property" Path="\Package.Connections[Excel Connection Manager].Properties[Description]" ValueType="String"> <ConfiguredValue> </ConfiguredValue> </Configuration> <Configuration ConfiguredType="Property" Path="\Package.Connections[Excel Connection Manager].Properties[ExcelFilePath]" ValueType="String"> <ConfiguredValue>\\domain.co.uk\dfs\w\WinningProject\ServiceRequestLoadTest\SRUpdate.xls</ConfiguredValue> </Configuration> <Configuration ConfiguredType="Property" Path="\Package.Connections[Excel Connection Manager].Properties[ExcelVersionNumber]" ValueType="Int32"> <ConfiguredValue>3</ConfiguredValue> </Configuration> <Configuration ConfiguredType="Property" Path="\Package.Connections[Excel Connection Manager].Properties[FirstRowHasColumnName]" ValueType="Boolean"> <ConfiguredValue>1</ConfiguredValue> </Configuration> <Configuration ConfiguredType="Property" Path="\Package.Connections[Excel Connection Manager].Properties[InitialCatalog]" ValueType="String"> <ConfiguredValue> </ConfiguredValue> </Configuration> <Configuration ConfiguredType="Property" Path="\Package.Connections[Excel Connection Manager].Properties[Name]" ValueType="String"> <ConfiguredValue>Excel Connection Manager</ConfiguredValue> </Configuration> <Configuration ConfiguredType="Property" Path="\Package.Connections[Excel Connection Manager].Properties[Password]" ValueType="String"> <ConfiguredValue> </ConfiguredValue> </Configuration> <Configuration ConfiguredType="Property" Path="\Package.Connections[Excel Connection Manager].Properties[ProtectionLevel]" ValueType="Int32"> <ConfiguredValue>1</ConfiguredValue> </Configuration> <Configuration ConfiguredType="Property" Path="\Package.Connections[Excel Connection Manager].Properties[RetainSameConnection]" ValueType="Boolean"> <ConfiguredValue>0</ConfiguredValue> </Configuration> <Configuration ConfiguredType="Property" Path="\Package.Connections[Excel Connection Manager].Properties[ServerName]" ValueType="String"> <ConfiguredValue>\\domain.co.uk\dfs\w\WinningProject\ServiceRequestLoadTest\SRUpdate.xls</ConfiguredValue> </Configuration> <Configuration ConfiguredType="Property" Path="\Package.Connections[Excel Connection Manager].Properties[UserName]" ValueType="String"> <ConfiguredValue> </ConfiguredValue> </Configuration>
Any suggestions gratefully received.
Thanks
Brad
Report Abuse
14 May 2008, 12:57 PM UTC
Manikandan S
Posts 369
Re: Problem with Excel Connection Manager
Was this post helpful ?
Did you get the success message when you click the Test Connection button when you create the connection ?
Bookmark:Digg Del.icio.us Reddit
