Wednesday, 13 November 2013

SSIS Excel Connection Manager Error : SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER

SSIS Excel Connection Manager Error : SSIS Error Code DTS_E_OLEDBERROR
Error Message :
I am trying to write my first BI package in VS 2005 to do a simple excel to SQL Server 2005 load. I have a simple dataflow containing an excel source and sql server destination. The Excel source is connected to an Excel 2003 worksheet. My machine is vista -64bit.
While debugging I get the following errors which do not help me much...can anyone tell me what I am doing wrong?

1). After "Progress Validating"
[Excel Source [1]] Error: 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.

2). After "Validation is Complete"
[Connection manager "Excel Connection Manager"] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040154. 

An OLE DB record is available. 
Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".

SSIS package "Package1.dtsx" starting.
Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.

Error: 0xC0202009 at Package1, Connection manager "Excel Connection Manager": SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040154.

An OLE DB record is available. 
Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".

Error: 0xC020801C at Data Flow Task, Excel Source [1]: 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.

Error: 0xC0047017 at Data Flow Task, DTS.Pipeline: component "Excel Source" (1) failed validation and returned error code 0xC020801C.

Error: 0xC004700C at Data Flow Task, DTS.Pipeline: One or more component failed validation.

Error: 0xC0024107 at Data Flow Task: There were errors during task validation.
SSIS package "Package1.dtsx" finished: Failure.

Solution :
This issue is occuring because you are having a 64 bit OS and Office Excel (including most of the office products) does not have 64bit provider and hence the error above on Excel connection.
You can solve this issue by setting Run64BitRunTime property as False.


1 comment:

  1. Thank for your information it work for me..

    /ts

    ReplyDelete

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts