64-bit Considerations for SQL Server Integration Services

Herewith my best effort to restate the contents of the Books Online topic, 64-bit Considerations for Integration Services, more simply and cleanly. Unfortunately the BOL topic has become quite messy over time, as one bandage is applied over another, and the costs of localization prevent me from trashing it and starting over. Please see the link for more details on any of the following points.

Not much has changed in 64-bit support from SQL Server Integration Services 2005 to 2008. At least you no longer have to worry whether scripts are precompiled or not.

Potential problems in a 64-bit setup

• 32-bit tools not installed. The 32-bit runtime and tools are not automatically included when you install 64-bit Integration Services. If you have packages that need to run in 32-bit mode, then you have to select an additional option on the Feature Selection page of Setup. On x64, this can be BIDS or Management Tools - Complete; since BIDS is not available on Itanium, on Itanium you must check Management Tools - Complete.
• 32-bit tools run by default. When the 32-bit tools are also installed, the path to the 32-bit tools appears BEFORE the path to the 64-bit tools in the PATH environment variable. Therefore, if you just type "dtexec" at the command prompt, for example, you are running the 32-bit version of the tool. Your options are to type the full 64-bit path, make the 64-bit directory the current directory first, or change the order in the PATH environment variable. Our testing has not shown any negative side-effects to changing the order of the PATH, but I make this suggestion cautiously.

Features that DON'T work on any 64-bit operating system (x64 or Itanium)

• DTS. You cannot run DTS packages in 64-bit mode on x64, or at all on Itanium. Therefore you also cannot use the Execute DTS 2000 Package task in these circumstances. Of course on x64 you can run DTS packages, or SSIS packages that run DTS packages, in 32-bit mode, after manually installing the optional DTS run-time support. For more information, see my post DTS support in SQL Server 2008 "Katmai".
• Excel, Access, Jet. You have to run packages that use the Jet provider (Access and Excel) in 32-bit mode.
• SQL Server Compact. You have to run packages that use the SQL Server Compact provider in 32-bit mode.
• Logging to SQL Server Profiler. You can only use package logging to SQL Server Profiler in 32-bit mode.

Features that DON'T work on the Itanium 64-bit operating system

• BIDS.
• DTS.

Integration Services tools that ARE available in a 64-bit version

• Runtime.
• dtexec.
• dtutil
• SQL Server Import and Export Wizard (dtswizard).

Integration Services tools that are NOT available in a 64-bit version

• Execute Package Utility (dtexecui). After using this tool to develop and test a dtexec command line, make sure that you test the command line for 64-bit execution by using the 64-bit version of dtexec.

Considerations when designing packages in BIDS

• Need both 32-bit and 64-bit providers. 32-bit BIDS always requires and uses the 32-bit version of a provider at design time. However when running the package in 64-bit mode, it automatically switches to the 64-bit version of the provider.
• Packages run in 64-bit mode by default. Use the Run64BitRuntime setting on the Debugging page of Project Properties to change this setting. The default value is True, which is simply ignored on a 32-bit computer.

Considerations when running packages by using SQL Server Agent

• Bitness of Agent = bitness of package execution. If you've installed 64-bit agent, it calls the 64-bit version of dtexec when running a package; if you've installed 32-bit Agent, it calls the 32-bit version of dtexec.
• In SQL Server 2008, you can now change this default. To run a package in 32-bit mode from a 64-bit version of SQL Server Agent, select Use 32 bit runtime on the Execution options tab of the New Job Step dialog box.

-Doug