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 Server\90\DTS\Binn