Administering SQL Server 2005 Integration Services
October 31, 2007
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:
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
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
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 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:
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.
SQL Server 2005 64-bit installs all the possible design and run components of SSIS, placing the 32-bit-only versions in "Program Files (x86)" and the 64-bit versions in "Program Files."
Some of the compatibility issues and limitations of the 64-bit version of SSIS include
When using a mixed environment of 64-bit and 32-bit SQL Server 2005 Integration Services, you must take care to ensure that compatibility issues do not arise.
There are also some package development issues that need to be taken into account; they're covered in Chapter 11.
Managing Integration Services
Much of the management of Integration Services revolves around packages. The tasks that you need to perform as database administrator are essentially package tasks, including creating, storing, and running packages, and other package-specific tasks.