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.

DTS Package Migration Wizard

SQL Server 2008 ships with a way to migrate simple packages to SSIS. With an Integration Services project open in Business Intelligence Development Studio (BIDS), right-click the Packages folder and select Migrate DTS 2000 Packages to launch the DTS Package Migration Wizard. Once launched, the wizard will quickly walk you through a migration process which can migrate most DTS tasks. The migrated packages will be added to the Integration Services project that is currently open. You can also start the DTS Package Migration Wizard from SQL Server Management Studio or from the command prompt.

Figure 3: DTS Package Migration Wizard (click for larger image)

DTS xChange

DTS xChange (http://www.dtsxchange.com) is an enterprise solution offered by a Microsoft partner, Pragmatic Works Software, which migrates DTS packages to Integration Services while applying a series of best practices rules to the packages.

Note. DTS xChange can migrate DTS packages to the SQL Server 2005 or 2008 Integration Services package format.

The solution is broken into three pillars:

• Profile – DTS xChange Profiler helps you estimate your migration project in hours and dollar cost whether you choose to use an automation tool or not.
• Convert – DTS xChange will migrate your packages, applying rules to each DTS package as it migrates them to enforce best practices.
• Monitor – The SSIS Performance Warehouse is a software development kit (SDK) to help you get the most out of your new Integration Services environment. It contains a series of reports and a data warehouse to monitor your Integration Services package execution.

DTS xChange Profiler

The DTS xChange Profiler feature allows you to profile how large of a migration effort you have to completely migrate to Integration Services in terms of dollars and hours. The process allows you to specify how long you believe each type of task will take you to migrate, whether you choose to use DTS xChange or manually re-engineer the package. Then, a report is generated with the migration cost in terms of dollars and hours to migrate each package and the total cost as shown below.

Figure 4: Migration report in DTS xChange (click for larger image)

DTS xChange Package Migration

The core component of DTS xChange is the actual migration of the packages. Prior to migrating the packages, you can choose between 8 best practice rules that Pragmatic Works has implemented over thousands of packages for its customers. These rules will enable you to truly capture the full benefit of Integration Services and realize some of your investment in the new platform. (Some of these best practice rules can also be applied to new packages you create in the SSIS environment by using a separate tool called SSIS Wiz by Pragmatic Works.) DTS xChange will also migrate many of the tasks that the built-in migration wizard cannot migrate, like Dynamic Properties Tasks.

Figure 5: Specifying migration rules in DTS xChange (click for larger image)

Migration of hundreds of packages is a fast process with DTS xChange. As shown below, packages are also validated as they are migrated to ensure that the package will work in production once you click the start button. The program checks to ensure that files exists, that table names that you think are there have actually been deployed, and that your credentials will also work in the package.

Figure 6: Migrating packages in DTS xChange (click for larger image)

SSIS Performance Warehouse

The last component of DTS xChange is a reporting and analytics component called the SSIS Performance Warehouse. This component is a software development kit (SDK) which will send auditing information about your package runtime and statistics into a data warehouse for future inspection. There is also a series of reports and a cube that goes on top of the data warehouse for easy viewing.

Obtaining DTS xChange

You can download DTS xChange from http://www.DTSxChange.com. The demo version can migrate up to 5 packages for free and profile your entire environment.

Conclusion

SQL Server Integration Services provides a robust way to process millions of rows of data. The platform is highly extensible and performs much faster than the previous DTS platform. Migration of old DTS packages can be done two ways: through the built-in Package Migration Wizard, which handles simple package migrations; and through DTS xChange, which handles both simple and complex DTS migrations, while applying best practices to the migrated packages.