Two Different DTExec.exe Apps
When you install SQL Server SSIS on a x64 machine, you actually get two different versions off DTExec.exe installed. This is the program that is used to execute SSIS packages. If you decide to call one of these directly or are creating a script to run on a regular schedule, make sure you are calling the right one! The command line API is identical between them. You can tell which version you are running by typing in DTEXEC.EXE in your command line window.
For a standard C: drive installation, the x64 version is typically located here:
C:\Program Files\Microsoft SQL Server\110\DTS\Binn\DtExec.exe
The x86 version is located here:
C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn
Note that the folder “110” above refers the version of SQL Server installed (version 11.0). Here’s a small list of the folder names for the versions of SQL Server:
- 90 = SQL Server 2005
- 100 = SQL Server 2008
- 105 = SQL Server 2008 R2
- 110 = SQL Server 2012
- 120 = SQL Server 2014
- 130 = SQL Server 2016
Not finding DTExec.exe? Make sure you’re not looking in the folder for SQL Server Express, if it is installed. You may have a newer version of SQL Server Express installed than your standard installation of SQL Server.
Two Different Import and Export Wizards
Know that there are two different wizards that can be used with the SSIS engine to move data. The x86 and x64 versions of DTSWizard.exe are located in the same folders as DTExec.exe (above). They can be launched from the Start menu or from the command line. Just be aware which one you are calling!
x64 Version:
C:\Program Files\Microsoft SQL Server\110\DTS\Binn\DTSWizard.exe
x86 Version:
C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\DTSWizard.exe
DTExecUI.exe is x86 (32-Bit) Only!
DTExecUI.exe is a great app for building up a command line that you can copy into a script to be used with DTExec.exe. However, beware of the Execute button! It will call the x86 version of DTExec.exe only!
You’d think Microsoft would make a simple update to this app (such as a checkbox) to allow it to run packages in x64 mode but not yet.
As well, this utility was not updated to execute SSIS packages located in the new SSIS catalog. It only works with packages located in the classic SSIS Package Store, the MSDB database, or packages that were file-deployed. Perhaps Microsoft is planning to eventually drop this tool in the future.
Running SSIS packages in Visual Studio
If you are creating temporary packages in Visual Studio to run them once to move data, you should be aware of whether you want them to run in x86 or x64 mode. By default, BIDS or SSDT will run your SSIS packages in x64 mode for higher performance.
However, sometimes you need your packages to run in x86 mode. For example, if your package is reading/writing with Office and you don’t have x64 drivers installed, you must run the package in x86 mode. As well, especially with BIDS, if you want to debug and use breakpoints with your C# code in your Script Task or Script Component, you must debug your package in x86 mode or else breakpoints will be skipped over.
To change this setting, simply right-click the SSIS project in the Solution Explorer window and click Properties. Expand the Debugging tab and set Run64BitRuntime to True or False.
Reading and Writing to Excel and Access – x86 or x64?
Many times, desktop machines and servers only come with the x86 versions of Office drivers. If your packages read or write to Excel documents or Access databases, you may need your package to run in x86 mode. Alternately, you can download the x64 Office drivers or install the x64 version of Office.
You may need to do some digging about how to install x86 and x64 versions of the Access and Excel drivers on the same machine. Remember, you can only install the x86 or x64 versions of Office on a machine – not both.
If your package is failing on a deployed server, see if it errors when reading/writing to Excel or Access. Try rerunning the package in the opposite mode (x86 or x64) and see if the error goes away. It’s a common problem for SSIS developers.
