question

DaveF33-2356 avatar image
0 Votes"
DaveF33-2356 asked cooldadtx answered

Deployed SSIS packages fail when loading Excel (Microsoft.ACE.OLEDB.12.0 is not registered)

Greetings,

My SSIS Packages were migrated (imported) to a new server (from VS 2012 to VS2019) and then deployed on the new server. I use SSMS v18 to view the deployed SSIS packages.

Problem: The SSIS packages I have that load Excel files with data work fine when I execute them from within Visual Studio (v 2019 and set to 32-bit). However, when I deploy them and then attempt to execute them in from SSMS (2018), they fail.

One common error is this: “…The Requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered. If the 64-bit driver is not installed run the package in 32-bit …”
Is there a way to configure them from the Integrated Service directory in SSMS to run as 32-bit (if that is what I need to do)? What do I need to do to make these packages work again?

Many Thanks for your suggestions. I'm on my own with this one.


sql-server-integration-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

cooldadtx avatar image
0 Votes"
cooldadtx answered

Sounds like you're running into the x64 issue. VS 2019 is x86 and therefore you probably installed the Office x86 version of the driver so it works. However SSIS is running x64 and you don't have the x64 driver installed so it fails to find it. Can you install the x64 version of the driver on your SQL Server instance? That is the way to go but note you cannot install both Office x86 and x64 on the same machine as mentioned here. But since you need to install it on your SQL Server and SQL is most likely x64 this shouldn't be an issue.

If for some reason you cannot install and use the x64 version of the driver then you have to tell SSIS to run your package in x86 mode instead. If you set the package properties (Debugging) Run64BitRuntime to false then that runs under x86 mode. If you're running your SSIS package in project deployment mode then there is a 32-bit flag you have to specify so SSIS runs using the x86 toolset. If you're using SQL Agent then you have to ensure you point it to the x86 version of DTExec instead. There are several articles on this but I haven't tried it myself. Here's one such article.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.