Background
SSIS is one of the best ETL tools available in the market and it can load a large amount of data from any heterogeneous data source whether structured, unstructured, application, cloud or real-time data. This post discusses the solution of a common problem that usually arises while importing data from Excel data source.
Problem
Beginners, while doing the extract, face some weird issues, which seem very fuzzy to solve at first. Such a problem is while doing extract from Excel data source. After running, the application shows error:
at Package, Connection manager “Excel Connection Manager”: SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available.Error: 0xC020801C at Data Flow Task — Excel File, Excel Source [1]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager “Excel Connection Manager” failed with error code 0xC00F9304. There may be error messages posted before this with more information on why the AcquireConnection method call failed.Error: 0xC0047017 at Data Flow Task — Excel File, SSIS.Pipeline: component “Excel Source” (1) failed validation and returned error code 0xC020801C.Error: 0xC004700C at Data Flow Task — Excel File, SSIS.Pipeline: One or more component failed validation.
And the extraction failed.
The reason for this run-time error is that Excel does not have a 64-bit driver which is selected by default in SSIS running on 64bit OS. So, the solution is also hidden behind the reason, change the driver from default and you are good to go. Steps to solve the issue are given below.
Step 1
Navigate to Project-> [PROJECT_NAME] Properties.
Step 2
Navigate to “Debugging” option from left panel and from Right panel, change Run64BitRuntime value to false.
That’s it!! Keep extracting!!
CodeProject
