Recommended by 0 users
As mentioned before, DTExecUI.exe is a 32-bit application. Therefore, whenever you execute a package from DTExecUI.exe, it will execute in 32-bit mode and potentially take longer to execute than if you were executing it on your development machine. Much of the reason for this is data must be marshaled back and forth between 32-bit mode and 64-bit mode, and the amount of memory available differs. Also, Visual Studio is a 32-bit process, so you will need to run your packages out of the Visual Studio debug mode to get a true 64-bit run. To get around this problem, you can go to the Command Line page of this tool, copy the command out of the window, and paste it into a command prompt, prefixing dtexec.exe in front of it.
DTExec comes in two flavors: 32-bit and 64-bit. The 32-bit version is stored in the \Program Files (x86) directory, and the 64-bit version is stored in the main \Program Files directory. Occasionally, we have seen issues where the environment variables used in configurations have issues and point to the wrong C:\Program Files\Microsoft SQL Server\100\DTS\Binn\ directory. You can fix this issue by right-clicking My Computer from your desktop and selecting Properties. From the Advanced tab, select Environment Variables. In the System Variables window, select the Path variable and click Edit. In the window that appears, you will see the path to the Binn directory. Ensure that the path is set to \Program Files and not \Program Files (x86), and click OK. After doing that, you can feel confident when typing DTExec at a command prompt that you’re executing the 64-bit version (the version of DTExec is shown in the first few lines of package execution). Note that this applies only to 64-bit machines.
A particularly annoying quirk (at the time of publication) is the lack of an MDAC driver for the 64-bit architecture. The impact of this is that you can’t execute packages in 64-bit mode if they refer to anything that uses a Jet driver, in particular Access and Excel. If you need to do this, you can execute the package using the 32-bit version of DTExec.exe. Another option in SQL Server Data Tools is to right-click the project, select Properties, and set the Run64BitRuntime to false in the Debugging page. This will set packages inside the project to run in 32-bit mode when debugging.
Aside from the few quirks of the 64-bit architecture and SSIS, the benefits are incredible. Keep in mind that SSIS is very memory intensive. If you’re able to scale up the memory on demand with a 64-bit architecture, you have a truly compelling reason to upgrade. Even though tools like DTExecUI are not 64- bit ready, scheduled packages will run under 64-bit mode. If you want a package to run under 32-bit mode, you have to add the step to run the 32-bit DTExec from the scheduled job by going to the runtime option in the Execution Options tab in SQL Server Agent.