Error when importing Excel in SSIS

2

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??

sql-server
excel
ssis
oledb
etl
asked on Stack Overflow Jan 30, 2018 by asmgx • edited Jan 30, 2018 by Hadi

5 Answers

2

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:

Additional information

answered on Stack Overflow Jan 30, 2018 by Hadi
1

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.

answered on Stack Overflow Jan 30, 2018 by Sandip - Full Stack Developer • edited Jan 30, 2018 by Sandip - Full Stack Developer
1

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.
answered on Stack Overflow Jan 30, 2018 by Mr. Bhosale • edited Jan 31, 2018 by Yahfoufi
1

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:

answered on Stack Overflow Jan 31, 2018 by Yahfoufi
1

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.

Property Pages Window


User contributions licensed under CC BY-SA 3.0