SSIS OLEDB.12.0 issue on SQL server2016/visual studio 2015

2
votes

I face a problem about SSIS to import an Excel file.

  • It works when I use a Visual studio 2015 to develop and run SSIS with AccessDatabaseEngine(32-bit).

  • It also works when I use Task-->import on SQL Server 2016 and select excel 2007-2010(xlsx) with AccessDatabaseEngine(32-bit).

But I doesn't work when I run SSIS package under the SSISDB catalog on SQL Server with the AccessDatabaseEngine(32-bit) is shown error about:

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

After that, I tried to uninstall AccessDatabaseEngine(32-bit) and install AccessDatabaseEngine(64-bit) then It works when I run SSIS package under the SSIDDB catalog but I doesn't work when I run with Visual studio or run with Task-->import on SQL Server.

Could you suggest what is the best solution to fix this problem?

Thank you

2
sql-servervisual-studio-2015ssissql-server-2016ssis-2016
The error message says "run the package in 32-bit mode" You should try that. – Nick.McDermaid
I also set a Run64BitRuntime in the Visual Studio before deploy project to SSISDB catalog form Ture --> False and build it. but when I tried to run with SSISDB catalog It doesn't work TT – alice
I believe you need need to set it when you run it, not when you deploy it. How are you running it? From a right click? I don't have one to test here, but you should be able to tell it at runtime – Nick.McDermaid
Yes, I run it by right click on a package under SSISDB catalog and select "execute". I'm not sure how to set it a 32-bit when execute on the ssis catalog. – alice
OK, I found 32-bit runtime on the Advance tab when I right click and select execute. Thank you so much Nick :) – alice

2 Answers

1
votes

enter image description here

The issue solve by click 32-bit runtime in the advance tab when execute a package under SSISDB catalog.

0
votes

to run with visual studio try to install AccessDatabaseEngine_X64