Writer: Brian Knight

Published: July 2008

Applies To: SQL Server 2005, 2008

Introduction

SQL Server Integration Services (SSIS) brings a revolutionary

concept of enterprise-class ETL to the masses. The engine is robust enough to

handle hundreds of millions of rows with ease, but is simple enough to let both

developers and DBAs engineer an ETL process. In this whitepaper, you will see

the benefits of migrating your SQL Server 2000 Data Transformation Services

(DTS) packages to Integration Services by using two proven methods. You will

also see how you can run and manage your current DTS packages inside of the SQL

Server 2005 and 2008 management tools.

Why Upgrade from DTS to Integration Services?

Although SQL Server 2008 Integration Services can be considered

the fourth iteration of a SQL Server ETL tool, it’s dramatically different from

DTS. The architecture has changed drastically in Integration Services to

support an in-memory ETL method that can support a load of millions of rows

with little effort. In DTS, a typical ETL scenario consisted of loading one or

multiple staging tables, and then applying a series of updates prior to the

final load of the data warehouse or OLTP system, as shown in the figure.

Figure 1: Architecture of an ETL solution with Data

Transformation Services (click for larger image)

The constant updates to staging tables prior to that final load were

very taxing on database servers. In Integration Services, the architecture has

changed, and you can do most of these functions in memory without even writing

the data to a staging table. There are a number of built-in components that

help quickly perform many of the functions that were done through Transact-SQL

updates in past ETL processes.

Figure 2: Architecture of an ETL solution with Integration

Services (click for larger image)

When you upgrade to SSIS, you may see a dramatic improvement in

performance. For example, we took a simple DTS package which pulls one million

rows from a comma-delimited text file and writes that data to a SQL Server

table. In DTS, the average run time over five runs of the package was 33.2

seconds. The same package, after it was upgraded to Integration Services, took

11.3 seconds on average when using a SQL Server destination component in the

Data Flow task, or 12.3 seconds on average when using a standard OLE DB

Destination.

Support for DTS in SQL Server 2008

DTS packages can still run as before when you have the SQL Server

2008 database engine installed. To accomplish this, you must have the Microsoft

SQL Server Backward Compatibility feature installed from the SQL Server

installation media or the Feature Pack Web page. This installs the DTS runtime

for SQL Server 2008, which works on 32-bit platforms only.

If you perfom an in-place upgrade from SQL Server 2000 to SQL

Server 2008, then all the DTS packages stored locally will be preserved as DTS

packages in SQL Server 2008. The upgrade process does not automatically migrate

DTS packages to the Integration Services format. To edit the DTS packages, you

must have the Microsoft SQL Server 2000 DTS Designer Component installed, which

is part of the SQL Server 2005 Feature Pack and is a separate download from the

Microsoft website.

You can manage your DTS packages from SQL Server Management

Studio under the Management à Legacy

à Data Transformation Services node.

Here, you can open existing DTS packages stored on the file system or in the msdb

database, or add additional packages to the server by clicking the Import

button. New DTS packages cannot be created from Management Studio, but old

packages can be modified and renamed.