Running SSIS packages in 32 bit mode in 64 bit server

The impetus for this blog is recent issue faced by use in running SSIS package from a job created in SQL Server agent. The package was locally developed and tested fine. After deploying package to server and trying to run it from SQL server agent however gave us the below error

The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine

We investigated the cause and found out that the reason was OS version is 64 bit and ACE provider was not supported in it. We had no clue how to fix it before somebody noticed the below property available inside the sql agent job step window

This fixed the error as setting the above would run the package under 32 bit mode and will support ACE provider used inside.

BIDS
—————————–
Similarly if you’re running the package inside bids you can find similar property inside project properties

Setting this would also have same effect as above

Command line
——————————————
If you’re choosing to execute the package from command line using dtexec and have to run it in 32 bit mode then you need to call 32 bit version of dtexec. Under default installation, it can be found at path C:Program Files (x86)Microsoft SQL Server90DTSBinn

Published by dataplatformcentral

6 thoughts on “Running SSIS packages in 32 bit mode in 64 bit server”

  1. The documentation for dtexec indicates that the /x86 switch is only valid for SQL Agent.

    "This option is only used by SQL Server Agent. This option is ignored if you run the dtexec utility at the command prompt."

    One must start with the correct "bit-ed" dtexec to get the proper results from the command line.

  3. Super thanks a million times. Been struggling with this issue for 2 days and been searching for an answer. Kudos for sharing. Can breath a sigh of relief now.

Comments are closed.