Return to Newsletter Contents...

What Happened to DTS?

by: Charles Tournear, Senior Consultant, MCSE, MCSD, CRCP, MCDBA, MCT 

Data Transformation Services (DTS) within SQL Server 2000 has provided developers with data extraction and manipulation capabilities for several years. DTS was replaced with SQL Server 2005 Integration Services (SSIS).  SSIS is not an updated version of DTS—it is an entirely new product. SSIS has been architected from the ground up to provide a high-performance, scalable data movement and integration platform. Given that SSIS is a new product compared to DTS, the upgrade path is actually more of a migration.

So what happens to existing DTS packages after upgrading to SQL Server 2005? 

The most important thing to remember about the DTS-to-SSIS upgrade is that after it is complete, your packages will continue to run. Scheduled jobs will still use DTSRUN and call the same packages from the same locations as they did prior to the upgrade.  If you upgrade an existing SQL 2000 installation all exiting SQL Server Agent jobs will be disabled, and will need to be enabled to continue to run.

When making the upgrade, you will need to determine how you will manage these legacy DTS packages in the future. The DTS runtime will continue to be available, and will have been updated to enable DTS packages to connect SQL Server 2005 data sources. But the DTS designer components are removed along with SQL Server 2000 Enterprise Manager.

You cannot connect to a SQL Server 2005 instance using the SQL Server 2000 Enterprise Manager. But SQL Server Management Studio, which is the SQL Server 2005 replacement for Enterprise Manager, supports DTS packages.  In the Object Explorer window, under the Management Legacy nodes, you will find Data Transformation Services. This is the equivalent of local packages. You can import packages and start the DTS-to-SSIS migration wizard from there, but to do any editing work or manage packages you must download and install the Microsoft SQL Server 2005 DTS Designer Components. With the full DTS Designer, you can create or edit DTS packages as you have done in the past.  SQL Server Management Studio does not support Meta Data Services, so you will not be able to enumerate or edit packages stored there. Meta Data Services will continue to be available through the object model and DTSRUN, but there is no support from management tools.

Downloading DTS Designer Tools

The SQL Server 2005 Setup application will install an updated DTS runtime and command-line tools, but no DTS design environment will be available. The DTS Designer Components can be installed via a Web download from the Feature Pack for SQL Server 2005. The Feature Pack is a collection of useful tools and components for working with SQL Server 2005; you can find it at:

http://www.microsoft.com/downloads/details.aspx?FamilyID=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&DisplayLang=en

Brief Description

Download the November 2005 Feature Pack for Microsoft SQL Server 2005, a collection of standalone install packages that provide additional value for SQL Server 2005.

Microsoft SQL Server 2000 DTS Designer Components

The Microsoft SQL Server 2000 Data Transformation Services (DTS) package designer is a design tool used by developers and administrators of SQL Server 2005 servers to edit and maintain existing DTS packages until they can be upgraded or recreated in the SQL Server 2005 Integration Services package format. After installing this download, SQL Server 2005 users can continue to edit and maintain existing DTS packages from the Object Explorer in SQL Server 2005 Management Studio and from the Execute DTS 2000 Package Task Editor in Business Intelligence Development Studio, without needing to reinstall the SQL Server 2000 tools. The DTS package designer in this download was formerly accessed from the Data Transformation Services node in SQL Server 2000 Enterprise Manager.

Audience(s): Customer, Developer

X86 Package (SQLServer2005_DTS.msi) - 5083 KB

Once the DTS Designer Components have been installed, an additional step is required to enable package editing. During an installation of SQL Server 2005, a set of backward compatibility files are also installed. Unfortunately, the order of installation for these files along with the removal of SQL Server 2000 (or SQL Server 7.0) causes an inconsistency that disables the DTS Designer Components. To resolve the issue, use the Add or Remove Programs applet from Control Panel to repair the Microsoft SQL Server 2005 Backward Compatibility feature. Once the feature is highlighted within the Add or Remove Programs applet, select Change. This will launch a setup application that can be used to repair the installation. Once completed, the DTS Designer Components will function as intended and allow packages to be edited from within SQL Server Management Studio.

Migrating Packages

Existing DTS packages can be converted to SSIS using the Package Migration Wizard. When migrating, the existing DTS packages remain unchanged. The migration wizard uses a “best effort” strategy to generate new SSIS packages that replicate each DTS package’s functionality.

Migration Issues

SQL Server 2000 provided three storage options for DTS packages: the msdb database, the Meta Data Services repository, and the Windows file system.  SQL Server 2005 does not include support for the repository. As a result, the Package Migration Wizard cannot migrate DTS packages stored in the Meta Data Services repository.  These packages are inaccessible unless you open them in the SQL Server 2000 Meta Data Services repository and then save them either as structured storage files or in the msdb database.

SQL Server 2000 and 7.0 provided a single development and management tool for DTS, embedded within SQL Server Enterprise Manager. SSIS, however, uses separate tools for development and management, made available as part of SQL Server 2005’s workstation components. Business Intelligence Development Studio (BIDS) provides an integrated development. SQL Server Management Studio provides a unified tool for managing SQL Server, Reporting Services, SSIS, and more. Which tools are available on a given server depends on the role of the server and the installed client tools.

In some cases, the Package Migration Wizard cannot directly migrate functionality found within a DTS package into equivalent SSIS functionality. Some of the features available within DTS do not have direct equivalents within SSIS.  When the Package Migration Wizard migrates a given DTS package to SSIS, it converts tasks and package functionality directly whenever possible. If one or more DTS tasks do not have equivalents in SSIS, or will not convert cleanly, the migration wizard will encapsulate the tasks within an Execute DTS 2000 Package Task. The DTS functionality will be embedded into the task and package.  A more significant development effort should be pursued to replace the Execute DTS 2000 Package Task generated. In this case, the resulting Transform Data Task can be replaced with a Data Flow Task that uses a Character Map Transform to implement the needed uppercase conversion process.

Scheduling an SSIS package

Just like DTS in SQL Sever 2000, SSIS packages are scheduled using SQL Server Agent jobs.  However there is no schedule menu option on the package like there was in SQL 2000 Enterprise Manager that would automatically create the job for you.  To schedule a package under Management, SQL Server Agent, Jobs--Create a new job.

Under Steps, Add a new step and select SQL Server Integration Services Package from the Type list and set the properties for the execution of the package.

And lastly, Add a new schedule to the job and configure the schedule.

If you wish to learn more about working with SSIS packages and creating new packages in the BIDS interface consider taking the SSIS one day workshop at Result Data.

Go to Top  |  Return to Newsletter Contents