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:

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

---

Note - This problem is not unique to SSIS. Many other applications have limitations and provisos when you're installing on 64-bit platforms 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

• Business Intelligence Development Studio is not supported on Itanium 64-bit. This component is not supported on Itanium 64-bit computers and is not installed on this platform. It is installed and supported on AMD 64-bit computers.

• A limited number of native 64-bit tools is supported. The only native 64-bit SSIS tools are dtexec, dtutil, and the SQL Server Import and Export Wizard. All the other tools are 32-bit versions when SSIS is installed on 64-bit servers.

• The execute package utility is 32-bit. The UI version of the dtexec utility that runs packages from within SQL Server Management Studio, the dtexecui utility, is 32-bit and runs packages in 32-bit mode.

• The SQL Server message queue task is either 64-bit or 32-bit. The Message Queue task only runs in packages that are running in the same mode as the SQL Server installation. If a 64-bit version of SQL is installed, then the Message Queue task only runs in packages that are running in 64-bit mode. The Message Queue task does not run using the execute package utility, which is only 32-bit.

• The SQL Server Agent is only 64-bit. The SQL Server Agent runs in 64-bit mode on 64-bit platforms, so jobs with SSIS package-execution-type steps use the 64-bit dtexec utility and run in 64-bit mode. To run packages in 32-bit mode, you must use the operating-system-type step and use the 32-bit version of the dtexec in the command line.

• SSIS is not backward compatible with DTS jobs in 64-bit. The 64-bit SQL Server 2005 does not support any backward compatibility with DTS packages created in earlier versions of SQL.

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.