Running SSIS 32-bit drivers or tasks on a 64 bit machine

2009/12/11

This is an answer to a forum question on www.bidn.com

Running SSIS on a 64-bit machine has several caveat that developers need to be aware of. Tasks like the ActiveX task and Execute DTS 2000 Package Task only work either on a 32-bit machine or by running in 32-bit mode on a 64-bit machine. This is also the case for many data providers like Excel, Access, and many ODBC drivers. If you are working on a 64-bit machine don't worry there is a work around to keep yourself productive while still using 32-bit tasks and drivers.

Executing In BIDS

Right-click on the project file and select properties in your solution
Under the Debugging tab you will see a property called Run64BitRuntime which you need to change to true

Executing On Server

SQL Server 2008

Either in a SQL Agent job or by just executing the package by itself there is a tab called Execution options.
Select Use 32 bit runtime and the package will then run in 32-bit mode

SQL Server 2005

Use the 32-bit version of dtexec and execute your package from the command line which can be found in the Program Files (x86) folder.
Follow link to execute package from command line http://msdn.microsoft.com/en-us/library/ms162810.aspx.