DTExec February 24, 2014
On a 64-bit computer, Integration Services installs a 64-bit version of the dtexec utility (dtexec.exe). If you have to run certain packages in 32-bit mode, you will have to install the 32-bit version of the dtexec utility. To install the 32-bit version of the dtexec utility, you must select either Client Tools or Business Intelligence Development Studio during setup.
By default, a 64-bit computer that has both the 64-bit and 32-bit versions of an Integration Services command prompt utility installed will run the 32-bit version at the command prompt. The 32-bit version runs because the directory path for the 32-bit version appears in the PATH environment variable before the directory path for the 64-bit version. (Typically, the 32-bit directory path is :\Program Files(x86)\Microsoft SQL Server\100\DTS\Binn, while the 64-bit directory path is :\Program Files\Microsoft SQL Server\100\DTS\Binn.)
Can’t populate an Object variable with a recordset when using an Execute SQL Task connecting to an ODBC data source;
If you have a 64bit machine, with connections that only support 32bit (like Excel, Access and old ODBC connections) you will get an error like above. The solution is to run your package in 32bit mode. This can be done within Visual Studio for debugging or within SQL Server Management Studio for scheduled packages.
Right Click the SSIS project and select Properties in the context menu. Go to the Debugging pane and select false under Run64bitRuntime.
SQL Server Management Studio
Edit your job and then edit the right jobstep. Go to the Execution Options pane and check “Use 32 bit runtime”. This property will only effect the package (+child packages) called in this jobstep.
Note: For 32bit execution via dtexec.exe you need to pick the right folder:
32bit => C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe
64bit => C:\Program Files\Microsoft SQL Server\100\DTS\Binn\DTExec.exe
Fixing SQL Agent
You will need to edit the existing SQL Agent job to change the bittedness of the job step. This will be under the Configuration tab and then under the Advanced tab. Check/Uncheck the 32-bit runtime.
If you double-click a dtsx file from Windows Explorer, it is executed by the SQL Server 2008 Integration Services Package Execution Utility. The default fully qualified path for that tool is C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\DTExecUI.exe. In other words, it is a 32-bit product, so it doesn’t have any trouble running things in 32-bit mode by definition.