64 bit Considerations for Integration Services
The 64-bit editions of Microsoft SQL Server 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.
Installing Integration Services on 64 bit (64-bit) Computers
Integration Services Features Installed on 64 bit (64-bit) Computers
When you run SQL Server Setup and select only Integration Services for installation, Setup installs all available 64-bit Integration Services features and tools.
However, if you require Integration Services design-time features, you must install Business Intelligence Development Studio. To install Business Intelligence Development Studio, select Business Intelligence Development Studio during setup.
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.
If you have to run certain packages in 32-bit mode, you have to install the 32-bit versions of the Integration Services tools. To install the 32-bit versions of the tools, you must select either Business Intelligence Development Studio or Management Tools - Complete during setup.
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.)
64 bit (64-bit) Versions of Integration Services and Tools
The Integration Services runtime has a 64-bit version.
The following Integration Services tools also have 64-bit versions:
The dtexec utility (dtexec.exe)
The dtutil utility (dtutil.exe)
The SQL Server Import and Export Wizard (DTSWizard.exe)
On a 64-bit computer, selecting Integration Services during Setup installs only the 64-bit runtime and tools. If you have to run packages in 32-bit mode, you must also select an additional option to install the 32-bit runtime and tools:
If the 64-bit computer is running the x86 operating system, select Business Intelligence Development Studio or Management Tools - Complete.
If the 64-bit computer is running the Itanium operating system, select Management Tools - Complete.
Note
When you use the Execute Package Utility (dtexecui.exe) on a 64-bit computer to develop and test commands, remember that this 32-bit tool is running packages in 32-bit mode. Before you deploy or schedule these commands on a production server, you should use the 64-bit version of the dtexec utility to test the commands in 64-bit mode.
By default, a 64-bit computer that has both the 64-bit and 32-bit versions of an Integration Services command prompt utility installed will run the 32-bit version at the command prompt. The 32-bit version runs because the directory path for the 32-bit version appears in the PATH environment variable before the directory path for the 64-bit version. (Typically, the 32-bit directory path is <drive>:\Program Files(x86)\Microsoft SQL Server\100\DTS\Binn, while the 64-bit directory path is <drive>:\Program Files\Microsoft SQL Server\100\DTS\Binn.)
Note
If you use SQL Server Agent to run the utility, SQL Server Agent automatically uses the 64-bit version of the utility. SQL Server Agent uses the registry, not the PATH environment variable, to locate the correct executable for the utility.
To ensure that you run the 64-bit version of the utility at the command prompt, you can take one of the following actions:
Open a Command Prompt window, change to the directory that contains the 64-bit version of the utility (<drive>:\Program Files\Microsoft SQL Server\100\DTS\Binn), and then run the utility from that location.
At the command prompt, run the utility by entering the full path (<drive>:\Program Files\Microsoft SQL Server\100\DTS\Binn) to the 64-bit version of the utility.
Permanently change the order of the paths in the PATH environment variable by placing the 64-bit path (<drive>:\Program Files\Microsoft SQL Server\100\DTS\Binn) before the 32-bit path (<drive>:\ Program Files(x86)\Microsoft SQL Server\100\DTS\Binn) in the variable.
Support for Data Transformation Services on 64 bit (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 Support for SQL Server 2000 DTS in SQL Server 2008 R2.
On Itanium-based operating systems, there is also no 32-bit design-time or run-time support for DTS packages. Therefore you cannot create, view, modify, or run DTS packages on Itanium-based operating systems.
Designing Integration Services Packages on 64 bit (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. Also, the SQL Server Compact Provider, which connects to SQL Server Compact data sources, is not available in a 64-bit version.
Note
The ACE Provider and its Excel 2010 driver (64-bit, 32-bit) can be used with Integration Services under certain circumstances. For more information and to download the provider, see Microsoft Access Database Engine 2010 Redistributable.
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 use the Execute DTS 2000 Package task in a package that is running in 64-bit mode. The SQL Server 2000 Data Transformation Services runtime is not available in a 64-bit version.
In regards to package logging, you cannot use the SQL Server Profiler log provider in a package that is running in 64-bit mode.
Running Integration Services Packages on 64 bit (64-bit) Computers
Sometimes you might want to run packages in 32-bit mode on a 64-bit computer. You might want to run packages in 32-bit mode for the following reasons:
To run SQL Server 2000 DTS packages.
To use a managed .NET Framework Data Provider or native OLE DB provider that is not available in a 64-bit version.
To use a Script task or Script component that references other assemblies or COM components for which a 64-bit version is not available or not installed.
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 job step type of SQL Server Integration Services Package, the job invokes the dtexec utility. However, the version of the dtexec utility that the job invokes depends on what versions of SQL Server and SQL Server Agent have been installed and are running on the 64-bit computer:
The 64-bit versions of SQL Server and SQL Server Agent have been installed and are running on the computer. Then, a job step type of SQL Server Integration Services Package invokes the 64-bit version of the dtexec utility, and the package runs in 64-bit mode.
Note
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.
The 32-bit versions of SQL Server and SQL Server Agent have been installed and are running on the computer. Then, a job step type of SQL Server Integration Services Package invokes the 32-bit version of the dtexec utility, and the package runs in 32-bit mode.
|
.gif "Integration Services icon (small)")
Stay Up to Date with Integration Services