How to: Run SSIS in 32 bit Mode

SSIS is an excellent tools for extract, transform, and loading data. Nowadays, more and more machines that SQL Server runs on are 64 bit. However, there are some conditions that require SSIS to be run in 32 bit mode, such as

1. when SSIS tries to interact with Microsoft Excel or Access files that only support 32 bit.

2. ActiveX script task or Execute DTS 2000 package task.

3. Debug script tasks.

Sometimes if it is not set in a right runtime mode, you will get error messages like:

Error: 0xC00F9304 at Package, Connection manager "Excel Connection Manager": SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available.

Or even without error messages when you try to get your script task pause at a breakpoint inside the script. It seems never hit the breakpoint and does not pause as expected.

How to set 32bit runtime mode at PROJECT Level?

After the project is opened in BIDS, right-click the project on the Solution Explorer panel and then click Properties.

Then on the Property Pages, first select Debugging under “Configuration Properties” on the left. Then change the property for Run64BitRuntime to be FALSE. (The default value for Run64BitRuntime is True.)

 

Now every package under this Demo Debug Project is set to run in 32 bit.

How to set 32bit runtime mode at PACKAGE Level?

To set 32bit runtime mode at PACKAGE Level means that you can run just one package in 32 bit. This can be accomplished through SQL Agent job settings.

It is required to have X86 DTExec.exe installed in order to have SQL Agent job to execute in 32 bit mode. 32 bit version of DTExec.exe is often installed at

C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe (for SQL Server 2008)

C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe (for SQL Server 2012)

As shown below, when you schedule jobs, you can set the Execution Options in job steps to “Use 32 bit runtime”.

If you execute the package using DTEXEC via command-line scheduling process, you need to specify the 32-bit version of DTEXEC by explicitly running the DTEXEC.EXE from the right folder as shown:

"C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" /f C:\PackageFolder\Package.dtsx /Conf C:\ConfigFile.dtsConfig