Error when importing Excel in SSIS

Question

I am trying to import Excel sheet into SQL server through SSIS

The file was generated by SQL Server import task from another machine.

but when I try to run it the SSIS on my machine/Server

I get this error

TITLE: Package Validation Error

Package Validation Error

ADDITIONAL INFORMATION:

Error at Data Flow Task 1 [Source - 'detail_report (4)$' [53]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "SourceConnectionExcel" failed with error code 0xC0209302. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

Error at Data Flow Task 1 [SSIS.Pipeline]: Source - 'detail_report (4)$' failed validation and returned error code 0xC020801C.

Error at Data Flow Task 1 [SSIS.Pipeline]: One or more component failed validation.

Error at Data Flow Task 1: There were errors during task validation.

Error at MobUp02 [Connection manager "SourceConnectionExcel"]: The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered. If the 32-bit driver is not installed, run the package in 64-bit mode. Error code: 0x00000000. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".

Error at MobUp02 [Connection manager "SourceConnectionExcel"]: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. For more information, see http://go.microsoft.com/fwlink/?LinkId=219816

(Microsoft.DataTransformationServices.VsIntegration)

I googled that and changed Run64bitRuntime to False, yet problem still exist

Anyone know how to fix that??

Answer 1

As I was also facing this issue and I resolved it by installing this "Microsoft Access Database Engine 2010" software Below I am attaching the link to download that software you need to install in local as well as the server where that SSIS package will run Link-Cick here to download Microsoft Access Database Engine 2010

Answer 2

Select the Project (not the solution) in Solution Explorer window, then from the Visual Studio 2019 menu select:

Project -> Properties

The Property Pages window opens, then select:

Configuration Properties -> Debugging -> Debug Options -> Run64BitRuntime

Put that option on False and the error will disappear. It worked for me.

Answer 3

Try to Check the following:

1. Server BI Installed Version
2. Install same Excel version on your machine
3. Put Excel File on same path or Change Excel source File path with your machine path.

Answer 4

You can do 2 workarounds to solve importing excel using 64-bit ms office:

1. Using an OLEDB Source instead of Excel Source
2. Using SQL Server Import and Export Wizard

Check the following detailed article, it contains very helpful information:

• Importing data from 64-bit Excel in SSIS

Answer 5

The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered.

I think you should download the Access Database Engine 2010 32-bit:

• Microsoft Access Database Engine 2010 Redistributable

Additional information

• SSIS The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered
• The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered

Answer 6

Try to install access runtime 2010 and if not solve then access runtime 2007. It may solve your error.

Select 32 Or 64 bit as per your system.