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 R2. For information about command-line options for installing Integration Services, see How to: Install SQL Server 2008 R2 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:
You can install SQL Server Integration Services on a computer that has no previous instances of SQL Server.
You can install SQL Server 2008 Integration Services side-by-side with an existing instance of SQL Server 2005 Integration Services or of SQL Server 2000 Data Transformation Services (DTS) or of both. For more information, see Considerations for Updating Side by Side Instances of SQL Server 2008 and SQL Server 2008 R2.
Important
Data Transformation Services (DTS) is deprecated. For more information, see Data Transformation Services (DTS).
You can upgrade an existing instance of SQL Server 2005 Integration Services to SQL Server 2008 Integration Services. For more information, see Considerations for Upgrading Integration Services. For information about backward compatibility with earlier versions of Integration Services, see Integration Services Backward Compatibility
You can upgrade an existing instance of SQL Server 2000 Data Transformation Services (DTS) to SQL Server 2008 Integration Services. For more information, see Considerations for Upgrading Data Transformation Services. For information about backward compatibility with DTS, see Support for SQL Server 2000 DTS in SQL Server 2008 R2.
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:
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:
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 (SSIS) 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 (SSIS) 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 SQL Server 2000 DTS in SQL Server 2008 R2.