Chapter 5: Administering SQL Server 2005 Integration Services

SQL Server 2005 Integration Services (SSIS) replaces the SQL Server 2000 Data Transformation Services (DTS). SSIS provides for the following data services:

• Extraction

• Transformation

• Loading

This triumvirate of data services is frequently referred to as ETL. The process encapsulates the extraction of data from a source, the transformation of the data to suit the requirements of the application, and finally the load of the data into a destination. The transformations can include normalizing, sanitizing, merging, aggregating, and copying the data. The sources and destinations for the data can be SQL databases, third-party ODBC data, flat files, or any number of other data locations.

SSIS delivers high-performance ETL services with a rich set of tools for designing, testing, executing, and monitoring these integrations. The subject of SSIS is broken down into administration and development. This chapter addresses the topic of SSIS administration, and Chapter 11, "Creating Packages and Transferring Data," covers development.

What's New in Integration Services with Service Pack 2

There are a number of new features and enhancements to SSIS in SQL Server 2005 Service Pack 2. The principal changes are

• SQL Server Import and Export Wizard supports Office 12, providing connectivity to Access 2007 and Excel 2007.

• The package's interaction with external data sources is now logged for troubleshooting.

• Data flow components now support combo boxes for variables.

• The Execute SQL task now has a ParameterSize property for string values.

• The IsNull property of columns in the Script Component raises a warning when it is used incorrectly.

• The Lookup transformation reports the final count of cached rows.

In addition, the overall performance of SSIS has been improved in Service Pack 2. This allows faster package execution and higher data throughput.

Understanding Integration Services

SSIS is fundamentally a service for storing and executing packages. Administration and management are covered in this chapter; this principally is the administration and management of package storage and execution.

Much of the complexity in integrating disparate data stores is pushed down into the packages themselves. This level of detail is examined in Chapter 11.

Integration Services Object Model

The Integration Services Object Model is the collection of objects and the interactions between them. Objects within SSIS are

• Packages

• Control Flows

• Data Flows

• Connections

• Variables

• Event Handlers

• Log Providers

For the administration of SSIS, the main consideration is packages. The package object contains all the preceding objects and provides the most granular level of SSIS administration. The package is almost an atomic entity from the perspective of Integration Services.

Packages

Packages are the core structure for Integration Services. They contain the logic that defines how the data will be integrated. They contain control flows (that is, control tasks) and data flows (that is, data tasks). When packages are executed, the control and data flows are executed.

Packages can be stored in SQL Server 2005, to the Integration Services package store, or to a file system.

The internal objects within packages, ways to create packages in Business Intelligence Development Studio, and various options within packages are covered in Chapter 11.

Projects and Solutions

Packages are organized into solutions and projects, which are, respectively, containers used to facilitate the development of packages and groups of packages that accomplish a business purpose.

Projects are containers for the packages, and a single project can contain multiple packages. Solutions are containers for projects, and a single solution can contain multiple projects. The solutions, projects, and packages are created, tested, and deployed from SQL Server 2005 Business Intelligence Development Studio.

Although solutions and projects are used to organize the packages, ultimately, the packages are what SSIS uses to actually do work. These units are executed to actually manipulate data. Integration Services fundamentally does not interact with the overarching solution and project organizing containers.

This chapter does not address the creation of solutions and projects. This topic is covered in Chapter 11.

Integration Services Service

Integration Services is a Windows service that manages SSIS packages. The IS service handles the following:

• Starting and stopping packages

• Importing and exporting packages

• Managing the SSIS Package Storage

• Providing integration with SQL Server Management Studio

The service is not required for the design and running of packages, but it facilitates the monitoring of packages in the SSIS package store in the SQL Server Management Studio.

If the service is stopped, packages can still be executed using other tools such as the execute package utility, SSIS Designer, SQL Server Import and Export Wizard, or dtexec command-line utility.

64-Bit Limitations of Integration Services

Not all features of SSIS are available in 64-bit versions, so you need to take care with some issues. And there are further limitations when comparing AMD and Itanium 64-bit platforms.