Share via

Installing SQL Server Integration Services by Using Setup

  • Article

Note

For detailed information about how to use the SQL Server Installation Wizard to install Microsoft SQL Server 2005 components, we recommend that you review How to: Install SQL Server 2005 (Setup).

This topic contains only information specific to the installation of Microsoft SQL Server 2005 Integration Services (SSIS) by using the SQL Server Installation Wizard.

  • Selecting Components to Install
  • Specifying Integration Services File Locations
  • Installing the Integration Services Service
  • Installing Support for SQL Server 2000 Data Transformation Services Packages

You can also add Integration Services to an existing installation of SQL Server 2005. For more information, see How to: Add Integration Services to an Existing Instance of SQL Server 2005.

Note

Some SQL Server 2005 components that you may select for installation on the Components to Install page or the Feature Selection page of the SQL Server Installation 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 option installs the Integration Services components that are required for the SQL Server Import and Export Wizard. The Business Intelligence Development Studio option installs the Integration Services components that are required to design a package, but there are limitations: the Integration Services service is not installed, the Message Queue task is not functional, and you cannot run packages outside BI Development Studio. To ensure a complete installation of Integration Services, you must select Integration Services on the Components to Install page.

Note

For information about SQL Server 2005 features installed on a 64-bit computer, see Integration Services Considerations on 64-bit Computers.

Selecting Components to Install

Although you can install Integration Services alone, many data warehouse solutions require the installation of additional SQL Server 2005 components to enable the development, deployment, and administration of Integration Services packages. The following table describes these additional or optional SQL Server 2005 components.

Component For the Administrator For the Developer For Optional Use

Connectivity Components

   Components for communication between clients and servers, including network libraries for DB-Library, ODBC, and OLE DB.

X

X

Management Tools

   Interactive tools for managing SQL Server 2005, including SQL Server Configuration Manager, SQL Server Management Studio, SQL Server Profiler, and Replication Monitor.

X

Business Intelligence Development Studio

   The integrated development environment for business intelligence projects.

X

Software Development Kit

   Software that assists developers in creating custom solutions for SQL Server 2005.

X

SQL Server Database Services

   The SQL Server Database Engine and tools for managing relational and XML data, replication, and full-text search.

X

SQL Server Analysis Services

   SQL Server 2005 Analysis Services (SSAS) and tools used to support online analytical processing (OLAP) and data mining.

X

SQL Server Reporting Services

   The Report Server, for managing, executing, rendering, and distributing reports, and the Report Manager, a Web-based user interface that provides access to SQL Server 2005 Reporting Services (SSRS) components, including administrative tools and a user interface for viewing reports.

X

Documentation and Samples

   SQL Server Books Online documentation, programming reference material, and samples for all SQL Server 2005 components.

X

X

X

Installing Selected Components

You can install Integration Services alone or with additional SQL Server 2005 components, as described in the following procedures.

To install Integration Services only

  • On the Components to Install page of the SQL Server Installation Wizard, select only Integration Services.

    Note

    If you choose to install only Integration Services, then SQL Server 2005 client components, including command-line tools, connectivity components, programming models, management and development tools, documentation, and samples, are not installed by default.

To add or remove installation components by using the Components to Install page

  • On the Components to Install page, select the check boxes for those components that you want to add and clear the check boxes for those components that you want to remove.

    Note

    Selecting the Workstation components, Books Online and development tools installation option on the Components to Install page does not select the sample databases and programming samples. To install sample databases and programming samples, you must select these options manually on the Feature Selection page.

To add or remove installation components by using the Feature Selection page

  1. On the Components to Install page, click Advanced.

  2. On the Feature Selection page, select the check boxes for those components that you want to add and clear the check boxes for those components that you want to remove.

    Note

    The Feature Selection page is a good way to view a detailed list of installation options in the SQL Server Installation Wizard, including the components previously listed in this topic.

Specifying Integration Services File Locations

SQL Server 2005 Setup puts Integration Services program files and shared files in several locations. The default folder locations for Integration Services files are listed in the following table.

Integration Services files Default location

Integration Services core components and the dtexec command prompt utility

C:\Program Files\Microsoft SQL Server\90\DTS\Binn

Integration Services connection managers

C:\Program Files\Microsoft SQL Server\90\DTS\Connections

Integration Services enumerators

C:\Program Files\Microsoft SQL Server\90\DTS\ForEachEnumerators

Files in XML format that map data types from one database version or system to another (for example, from SQL Server 2000 to SQL Server 2005, or from SQL Server 2005 to Oracle). These mapping files are used by the SQL Server Import and Export Wizard.

C:\Program Files\Microsoft SQL Server\90\DTS\MappingFiles

Integration Services data flow components, consisting of sources, transformations, and destinations

C:\Program Files\Microsoft SQL Server\90\DTS\PipelineComponents

Integration Services run-time tasks

C:\Program Files\Microsoft SQL Server\90\DTS\Tasks

Components that are shared by all instances of SQL Server 2005 components

C:\Program Files\Microsoft SQL Server\90\Shared

This folder cannot be changed during installation.

Tools, documentation, and samples

C:\Program Files\Microsoft SQL Server\90\Tools

A single set of tools and documentation is installed for all instances of Analysis Services and other SQL Server components.

To change the default Integration Services file locations during installation

  1. On the Components to Install page, click Advanced.

  2. On the Feature Selection page, click a feature that supports a non-default installation path, and then click Browse.

  3. In the Installation path box of the Change Folders page, enter a new file location.

Important

You now have the option on the Feature Selection page to install certain SQL Server 2005 components to a location other than the default location of %ProgramFiles%\Microsoft SQL Server\90. This option is available for SQL Server 2005 Integration Services, SQL Server 2005 Notification Services, and Workstation Components (client tools, documentation, and samples). The corresponding switch to use when you install from the command prompt is installsqlshareddir.

Installing the Integration Services Service

Integration Services installs the Integration Services service, a new feature that provides several enhancements and that has no equivalent in SQL Server 2000 Data Transformation Services. The Integration Services service allows you to do the following in SQL Server Management Studio:

  • Monitor the execution of all Integration Services packages that are running on a computer.
  • Display a hierarchical view of Integration Services packages and folders that are stored in different physical locations.

Note

This service is not required to create, store, and execute SSIS packages.

The Integration Services service is installed and enabled by default when you select the SQL Server Integration Services component on the Components to Install page.

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

The service is registered to run under the Network Service account. (On Windows 2000, the service runs under the Local System account.) This setting cannot be modified through the SQL Server Installation Wizard, and is not affected by any selections that you make on the Service Account page of the wizard.

By default, the Integration Services service is configured to manage packages that are stored in the msdb database in a local, default instance of Database Engine. To manage packages that are stored in a named or remote instance of the Database Engine, or in multiple instances of the Database Engine, you have to modify the configuration file for the service. For more information, see Configuring the Integration Services Service.

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.

Installing Support for SQL Server 2000 Data Transformation Services Packages

When you select Integration Services for installation, Setup also installs support for SQL Server 2000 Data Transformation Services (DTS) packages, including the DTS runtime and DTS package enumeration in SQL Server Management Studio. Run-time support has been updated to allow DTS packages to access SQL Server 2005 data sources. If you are not installing Integration Services, but you need support for DTS packages, then you need to make sure that Legacy Components is selected on the Feature Selection page.

The backward compatibility files that are installed also include the SQL Server 2000 Analysis Services Processing task and its dependency, Decision Support Objects (DSO). However they do not include the SQL Server 2000 Data Mining Prediction Query task.

Note

SQL Server 2005 does not install the DTS package designer for editing DTS packages. For more information, see SQL Server 2005 Integration Services Backward Compatibility. DTS packages cannot be opened or edited in BI Development Studio.

When you select Integration Services for installation, the Package Migration Wizard is also installed. You can optionally use the Package Migration Wizard to upgrade SQL Server 2000 DTS packages to the Integration Services format. During migration, the wizard copies the DTS packages and then re-creates them in the Integration Services format. The source packages are left intact and are not modified. For more information, see Migrating Data Transformation Services Packages.

Note

The Package Migration Wizard is available in the Standard, Enterprise, and Developer Editions of SQL Server 2005.

Change History

Release History

15 September 2007
New content
  • Explained that, to manage the msdb database on remote or multiple instances, you must modify the default configuration file.

12 December 2006
New content:
  • Added link to topic about installing Integration Services on an existing instance of SQL Server.
  • Added link to topic regarding how to modify the configuration file for the Integration Services service to store packages in a location other than the default.

5 December 2005
New content:
  • Added note directing users to the Integration Services 64-bit features topic for more information.
Changed content
  • Improved the explanation of installation options for legacy components.