Considerations for Installing Integration Services

SQL Server provides a single Setup program to install any or all of its components, including Integration Services. Through Setup, you can install Integration Services with or without other SQL Server components on a single computer.

This topic highlights important considerations that you should know before you install Integration Services. Information in this topic will help you evaluate the installation options so that you can make selections that result in a successful installation.

This topic does not include instructions for starting Setup, using the Setup Wizard, or running Setup from the command line. For step-by-step instructions on how to start Setup and select components to install, see Quick-Start Installation of SQL Server 2008. For information about command-line options for installing Integration Services, see How to: Install SQL Server 2008 from the Command Prompt.

Preparing to Install Integration Services

Before you install Microsoft SQL Server Integration Services, review the following requirements:

Selecting an Integration Services Configuration

You can install SQL Server Integration Services in the following configurations:

Installing Integration Services

After you review the installation requirements for SQL Server and ensure that your computer meets those requirements, you are ready to install Integration Services.

If you are using the Setup Wizard to install Integration Services, you will use a series of pages to specify components and options. The following table lists only those pages in the Setup Wizard where the options that you select affect your installation of Integration Services:

Note

Some SQL Server components that you can select for installation on the Feature Selection page of the Setup Wizard install a partial subset of Integration Services components. These components are useful for specific tasks, but the functionality of Integration Services will be limited. For example, the Database Engine Services option installs the Integration Services components required for the SQL Server Import and Export Wizard. The Business Intelligence Development Tools (BIDS) option installs the Integration Services components required to design a package, but the Integration Services service is not installed and you cannot run packages outside of BI Development Studio. To ensure a complete installation of Integration Services, you must select Integration Services on the Feature Selection page.

Page

Recommendations

Feature Selection

Select Integration Services to install the Integration Services service and to run packages outside the design environment.

For a complete installation of Integration Services, together with the tools and documentation for developing and managing packages, select both Integration Services and the following Shared Features:

  • Business Intelligence Development Studio to install the tools for designing packages.

  • Management Tools - Complete to install SQL Server Management Studio for managing packages.

  • SQL Server Books Online to install documentation.

  • Client Tools SDK to install managed assemblies for Integration Services programming.

  • Client Tools Backward Compatibility to install the Execute DTS 2000 Package task, if you require support for DTS packages.

Many data warehousing solutions also require the installation of additional SQL Server components, such as the SQL Server Database Engine, SQL Server Analysis Services, and SQL Server Reporting Services.

Installing on a 64-bit Computer   On a 64-bit computer, selecting Integration Services 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.

Installing on a Dedicated Server for ETL   To use a dedicated server for extraction, transformation, and loading (ETL) processes, we recommend that you install a local instance of the SQL Server Database Engine when you install Integration Services. Integration Services typically stores packages in an instance of the Database Engine and relies on SQL Server Agent for scheduling those packages. If the ETL server does not have an instance of the Database Engine, you will have to schedule or run packages from a server that does have an instance of the Database Engine. This means that the packages will not be running on the ETL server, but instead on the server from which they were started. As a result, the resources of the dedicated ETL server are not being used as intended. Furthermore, the resources of other servers may be strained by the running ETL processes

Instance Configuration

Any selection that you make on the Instance Configuration page does not affect Integration Services or the Integration Services service.

You can only install one instance of the Integration Services service on a computer. You connect to the service by using the computer name.

By default, the Integration Services service is configured to manage packages that are stored in the msdb database in the instance of the Database Engine that is installed at the same time as Integration Services. If an instance of the Database Engine is not installed at the same time as Integration Services, the Integration Services service is configured to manage packages that are stored in the msdb database of the local, default instance of the Database Engine. To manage packages that are stored in a named instance or a remote instance of the Database Engine, or in multiple instances of the Database Engine, you have to modify the configuration file. For more information about how to modify this configuration file, see Configuring the Integration Services Service.

Server Configuration

Review the settings for the Integration Services service on the Service Accounts tab of the Server Configuration page.

By default, the Integration Services service is registered to run under the built-in Network Service account, and the Startup Type is Automatic. You do not have to enter a password for the built-in Network Service account.

By default, in a new installation, Integration Services is configured not to log events that are related to the running of packages to the Application event log. This setting prevents too many event log entries when you use the Data Collector feature of SQL Server 2008. The events that are not logged are EventID 12288, "Package started," and EventID 12289, "Package finished successfully." To log these events to the Application event log, open the registry for editing. Then, in the registry, locate the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\SSIS node, and change the DWORD value of the LogPackageExecutionToEventLog setting from 0 to 1.

Understanding the Integration Services Service

Integration Services installs the Integration Services service, which enables you to do the following tasks in SQL Server Management Studio:

  • Manage Integration Services packages and folders in a hierarchical view. Packages can be stored in an instance of the Database Engine or in the file system.

  • Monitor the execution of Integration Services packages that are running on a computer.

Important

The Integration Services service is not required to create, save, and run Integration Services packages.

The Integration Services service is installed when you select the Integration Services option on the Feature Selection page. When you accept the default settings on the Server Configuration page, the Integration Services service is enabled and its Startup Type is Automatic.

You can only install a single instance of the Integration Services service on a computer. The service is not specific to a particular instance of the Database Engine. You connect to the service by using the name of the computer on which the service is running.

To manage the service, use SQL Server Configuration Manager, or use the Services snap-in for Microsoft Management Console (MMC) by opening Administrative Tools in Control Panel. For more information, see Managing the Integration Services Service.

By default, the Integration Services service is configured to manage packages that are stored in the msdb database in the instance of the Database Engine that is installed at the same time as Integration Services. If an instance of the Database Engine is not installed at the same time as Integration Services, the Integration Services service is configured to manage packages that are stored in the msdb database of the local, default instance of the Database Engine. To manage packages that are stored in a named instance or a remote instance of the Database Engine, or in multiple instances of the Database Engine, you have to modify the configuration file. For more information about how to modify this configuration file, see Configuring the Integration Services Service.

Installing Integration Services on 64-bit Computers

Integration Services Features Installed on 64-bit Computers

Setup installs various Integration Services features based on the setup options that you select:

  • When you install SQL Server and select Integration Services for installation, Setup installs all available 64-bit Integration Services features and tools.

  • If you require Integration Services design-time features, you must also install Business Intelligence Development Studio.

  • If you require 32-bit versions of the Integration Services runtime and tools to run certain packages in 32-bit mode, you must also install Business Intelligence Development Studio.

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.)

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.

For more information, see 64-bit Considerations for Integration Services.

Support for Data Transformation Services on 64-bit Computers

There is no 64-bit design-time or run-time support for DTS packages. On a 64-bit computer, DTS packages, and Integration Services packages that run DTS packages, can run only in 32-bit mode. To install the 32-bit version of the dtexec utility (dtexec.exe), make sure to select Client Tools or Business Intelligence Development Studio during setup.

There is also no 32-bit design-time or run-time support for DTS packages on Itanium-based operating systems. Therefore, you cannot create, view, modify, or run DTS packages on Itanium-based operating systems.

Installing Support for SQL Server 2000 Data Transformation Services Packages

SQL Server 2008 does not install run-time support for DTS packages. You have to install this run-time support before you can run and manage DTS packages. For information about how to install support for DTS, see How to: Install Support for Data Transformation Services Packages.

Important

Data Transformation Services (DTS) is deprecated. For more information, see Data Transformation Services (DTS).

For more detailed information about how to use DTS with SQL Server 2008, see Support for Data Transformation Services (DTS) in SQL Server 2008.