64-Bit Issues

As mentioned earlier, DTExecUI.exe is a 32-bit application. Because of this, whenever you execute a package from DTExecUI.exe, it will execute in 32-bit mode even if you are running it on a 64-bit machine, which could potentially cause it to take longer to execute. Much of this is because data must be marshaled back and forth between 32-bit mode and 64-bit mode. To get around this problem, you can go to the Command Line page of this tool (shown earlier in Figure 9-10), copy the command out of the window, and paste it into a command prompt, prefixing dtexec.exe in front of it.

On a 64-bit machine, DTExec.exe 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, the environment variables may have issues and point to the wrong C:\Program Files\Microsoft SQL Server\90\DTS\Binn\ directory. You can fix this by right-clicking My Computer on the desktop and selecting Properties. Go to the Advanced tab and select Environment Variables to produce the screen shown in Figure 9-19. From the System variables section in the lower portion of the window, select the Path variable and click Edit. In the next window, you will see several delimited paths to various folders on the system. Look for the path referring to the Binn directory mentioned earlier. Ensure here that this path is set to \Program Files and not \Program Files (x86) and click OK. After that, you can go to a command prompt, type DTExec.exe, and know that you’re executing the 64-bit version (the version is of DTExec.exe shown in the first few lines of executing a package).

Figure 9-19: The Environment Variables dialog box

Another issue (up to and including Service Pack 2 of SQL Server 2005) is that Visual Studio .NET, and thus BIDS, does not work on Itanium 64-bit (IA64) machines. Because of this, you’ll have to develop your packages on a 32-bit machine and then deploy them to your Itanium machine. Along with this Itanium issue, you may find that many IA64 connectivity drivers lag behind the release cycle of x86 and x64 architectures.

Another issue that you’ll experience is with the Script Task or component inside of SSIS. If you use this task in a 64-bit environment, you must precompile your code. You can do this by opening each Script Task or component and setting the PrecompileScriptIntoBinaryCode to True, as shown in Figure 9-20. After you do this, you must click the Design Script button and close the script to actually precompile the script.

Figure 9-20: Setting the PrecompileScriptIntoBinaryCode to True

A particularly annoying quirk is that (as of this writing) there is no Microsoft Data Access Components (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 Jet 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 BIDS is to right-click on the project, select Properties, and set the Run64BitRuntime to False in the Debugging page (see Figure 9-21). This will set packages inside the project to run in 32-bit mode when debugging.

Figure 9-21: Setting the Run64BitRuntime to False

The good news is that the drivers are constantly changing and improving. A good wiki on 64-bit issues with connectivity (which is updated very frequently) can be read at http://ssis.wik.is. Just because your particular driver doesn’t work today, that doesn’t preclude it from working next month as vendors update your driver.

A few quirks of the 64-bit architecture and SSIS have been mentioned here, but 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, there’s a truly compelling reason to upgrade. Even though tools such as DTExecUI.exe are not 64-bit ready, packages that are scheduled will run under 64-bit mode. If you want a package to run under 32-bit mode, you’ll have to schedule the step to run the 32-bit DTExec.exe from the scheduled job.