Integration Services Considerations on 64-bit Computers

The 64-bit editions of Microsoft SQL Server 2005 include Integration Services, but some Integration Services features are available only in 32-bit versions, or have limitations on 64-bit computers, or are not supported on Itanium-based operating systems.

For information about the features included in the various editions of SQL Server 2005, see Features Supported by the Editions of SQL Server 2005.

Installing Integration Services on 64-bit Computers

Integration Services Features Installed on 64-bit Computers

When you install SQL Server 2005 and select both Integration Services and Business Intelligence Development Studio, all available 32-bit and 64-bit design-time and run-time Integration Services features are installed. When you install Integration Services, you also install 32-bit run-time support for SQL Server 2000 Data Transformation Services (DTS) packages.

64-bit features are installed under the Program Files directory, and 32-bit features are installed separately under the Program Files (x86) directory. (This behavior is not specific to Integration Services or to SQL Server 2005.)

Important

Business Intelligence Development Studio, the 32-bit development environment for Integration Services packages, is not supported on the Itanium 64-bit operating system and is not installed on Itanium servers.

64-bit Versions of Integration Services Tools

The following Integration Services tools have 64-bit versions:

• The dtexec utility (dtexec.exe)
• The dtutil utility (dtutil.exe)
• The SQL Server Import and Export Wizard (DTSWizard.exe)

Note

When you develop and test commands by using the Execute Package Utility (dtexecui.exe) on a 64-bit computer, remember that this 32-bit tool is running packages in 32-bit mode. You should also test your commands in 64-bit mode by using the 64-bit version of dtexec.exe before you deploy or schedule them on a production server.

Message Queue Task Installation Issue

Only one version of the SQL Server Message Queue task can be registered with COM+. Therefore, if you have installed 64-bit SQL Server on a 64-bit computer, you can use the Message Queue task only in packages running in 64-bit mode; you cannot use the Message Queue task in packages running in 32-bit mode.

If you have installed 32-bit SQL Server on a 64-bit computer, you can use the Message Queue task only in packages running in 32-bit mode; you cannot use the Message Queue task in packages running in 64-bit mode.

Support for Data Transformation Services on 64-bit Computers

There is no 64-bit design-time or run-time support for Data Transformation Services (DTS) packages that were created in earlier versions of SQL Server. For more information, see SQL Server 2005 Integration Services Backward Compatibility.

Designing Integration Services Packages on 64-bit Computers

You cannot design packages in BI Development Studio or debug scripts in the Script task on Itanium-based operating systems.

When you run a package in 64-bit mode, you might not be able to connect to as many data sources as you can when you run a package in 32-bit mode. Some .NET Framework Data Providers and native OLE DB providers might not be available in 64-bit versions. For example, the Microsoft OLE DB Provider for Jet, which connects to Access databases and Excel spreadsheets, is not available in a 64-bit version.

The 32-bit SSIS Designer displays only 32-bit providers that are installed on the local computer. To configure a connection manager to use a specific 64-bit provider, you must install the 32-bit version of the provider on the development computer for use at design time. Even though the 32-bit version of the provider is installed, you can still run the package in 64-bit mode, both in the development environment and after deployment. The 32-bit and 64-bit versions of a provider have the same ID. Therefore, the SSIS runtime will select the appropriate version of the provider to use. For example, you run the package in the development environment on a 64-bit computer. By default, the package uses the 64-bit version of the provider because the default value of the Run64BitRuntime project property is True.

You cannot run packages that contain scripts—for example, in Script tasks or Script components—in 64-bit mode unless the scripts have been precompiled. By default, the value of the PreCompile property is True for both the Script task and the Script component.

You cannot use the Execute DTS 2000 Package task in a package running in 64-bit mode. The SQL Server 2000 Data Transformation Services runtime is not available in a 64-bit version.

Running Integration Services Packages on 64-bit Computers

Sometimes you may want to run packages in 32-bit mode in order to run uncompiled scripts, run SQL Server 2000 DTS packages, or use a managed .NET Framework Data Provider or native OLE DB provider that is not available in a 64-bit version.

Selecting 32-bit or 64-bit Package Execution in SSIS Designer

In the Project Properties of an Integration Services package, you can select 32-bit or 64-bit execution by setting the value of the Run64BitRuntime property on the Debugging page. By default, the value of this property is True. When the 64-bit version of the Integration Services runtime is not installed, this setting is ignored.

Note

The Run64BitRuntime project property applies only at design time.

Selecting 32-bit or 64-bit Package Execution in a SQL Server Agent Job

When you configure a SQL Server Agent job with a step of type SQL Server Integration Services Package, the job always runs the package in 64-bit mode on a 64-bit computer, because this step type invokes the 64-bit version of dtexec.exe to run the package. This assumes that you have installed and are running the 64-bit version of SQL Server 2005 and SQL Server Agent on the 64-bit computer. If you have installed and are running the 32-bit version of SQL Server 2005 and SQL Server Agent on the 64-bit computer, this step type invokes the 32-bit version of dtexec.exe to run the package.

If you want to run a package from a 64-bit SQL Server Agent job in 32-bit mode, select a job step type of Operating system, and enter a command line or use a batch file that invokes the 32-bit version of dtexec.exe. You can use the dtexecui.exe utility to create the command line, and then copy and paste the command line into the job step.

Change History

Release	History
15 September 2007	Changed content: Clarified the availability of data sources and how providers work in a 64-bit development environment.
5 December 2005	Changed content: Noted that Business Intelligence Development Studio does not run on the Itanium architecture. Noted that dtexecui.exe runs in 32-bit mode only, and that packages need to be tested in 64-bit mode. Described known issue with the Message Queue task. Noted that the Microsoft OLE DB Provider for Jet, used for Access and Excel databases, is not available in a 64-bit version.