SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Upgrading SQL Server 2000 DTS Packages to SSIS

By Brian Knight, (first published: 2005/12/27)

If you're like me and most companies, you probably have dozens if not hundreds of SQL Server 2000 DTS packages in you SQL Server environment. The idea of upgrading those packages to SQL Server 2005 SSIS can be daunting. The packages probably drive production loads and you've spent years stabilizing them and don't want that 4AM call. This article shows you your options for upgrading the packages automatically and what components won't be upgraded.

The Server and Workstation Upgrade

After you upgrade your SQL Server to SQL Server 2005, the packages carry over in place and are not upgraded as part of the process. All of your production jobs that call the packages are also carried over without change. Without touching the packages, you can continue to run the 2000 packages since the DTS runtime environment comes over. You'll be able to find your 2000 packages in the Microsoft SQL Server Management Studio under the Management node then Legacy -> Data Transformation Services. The problem is going to be that you'll need to install the SQL Server 2005 Feature Pack (specifically, the Package Designer for 2000) to modify the package after the upgrade. This was not installed by default to give you a smaller surface area in your install. After you upgrade the last node on your SQL Server, the SQL Server 2000 tools are completely removed.

Note: Support for the Metadata Repository has been removed in SQL Server 2005 so those packages will not come over. You will need to save those packages into a different storage area like MSDB or the file structure before upgrading your instance.

You can also use the SQL Server 2005 tools to connect to SQL Server 2000 databases to manage the server and packages. I've been using the 2005 tools since an early CTP to manage my entire SQL Server 2000 environment and have encountered very few issues even in beta. The problem will lie in if you chose not to upgrade your tools. You cannot manage or design against a SQL Server 2005 environment with the 2000 tools. In other words, the 2000 tools aren't forward compatible as in past releases of SQL Server.

Package Upgrade Options

The first step that I would recommend in your upgrade path is to run the SQL Server 2005 Upgrade Advisor. The Upgrade Advisor will report also against DTS packages. It will give you warnings and errors and identify specific packages that will cause you grief in the upgrade.

In SQL Server 2005 SSIS packages, you have a new task called Execute SQL Server 2000 Package task. Another way to inch into an upgrade is to create a SQL Server 2005 SSIS package with a single Execute SQL Server 2000 Package task that executes the old package until you can execute have time to upgrade each component. The task has the option to embed the 2000 package into the task itself so as you move the SSIS package, the DTS package comes with it. This tactic will be used later by the Upgrade Wizard for complex logic.

The best way to upgrade your packages is with the Package Upgrade Wizard. The wizard will leave the old package and calling jobs in place but clone the package with no job. The wizard isn't perfect though. It was developed to perfectly upgrade simple workflow and transforms written with the Import/Export Wizard or similar logic. If you have customized transforms like one that does a upper case transform, it will not be ported over exactly as is. If you have any complex transforms or tasks, a small 2000 DTS package that contains that step will be created and a Execute SQL Server 2000 Package task will be created to call that package. The package will be embedded in the task and you can modify the 2000 package by clicking Design Package inside that task (it won't show up in your legacy packages node). Using the wizard, I was able to upgrade about 75% of the packages (with simple logic) and 10% upgraded with issues like I just mentioned and the remainder had to have manual intervention.

There are some components that will not be compatible with SQL Server 2005. Just because they're not compatible though, doesn't mean there's not an upgrade path. The areas of special consideraExecute SQL Server 2000 Package tasktion are:

  • Dynamic Properties task has no 2005 task to port to. A placeholder Script Task will upgrade over but will not have any functionality. Your package will not work as expected until you upgrade the logic using the new Package Configuration option or expressions.
  • Analysis Services tasks are wrapped in a Execute 2000 Package task.
  • ActiveX Script tasks that take extensive use of the DTS object model will not be supported. For example, if you used the model to call other packages or loop, this will not port. You will want to implement some of the built-in controls for this like the For Each Loop container.
  • Global Variables are ported to package variables with no issues.
  • Data Driven Query tasks are not supported. The upgrade wizard will use a sub-2000 package to make this work.
  • Any custom task will be wrapped in a subtask as well.

It's important to note that long term support of SQL Server 2000 DTS is not in the cards. You need to spend this release of SQL Server 2005 getting off of DTS because I can't imagine support of DTS existing in the next release (code named Avalon). I hope some useful information to get you start down the path to upgrading your DTS packages to SSIS.

My New Book

Professional SQL Server 2005 Integration ServicesIn January, my new book comes out on SQL Server 2005 Integration Services from Wrox. You can pre-order it today on http://www.wiley.com or go to Amazon to get the better price :). This book will help you get past the initial learning curve quickly so that you can get started using SSIS to transform data, create a workflow, or maintain your SQL Server. Offering you hands-on guidance, you'll learn a new world of integration possibilities and be able to move away from scripting complex logic to programming tasks using a full-featured language.

What you will learn from this book

  • Ways to quickly move and transform data
  • How to configure every aspect of SSIS
  • How to interface SSIS with web services and XML
  • Techniques to scale the SSIS and make it more reliable
  • How to migrate DTS packages to SSIS
  • How to create your own custom tasks and user interfaces
  • How to create an application that interfaces with SSIS to manage the environment
  • A detailed usable case study for a complete ETL solution
Total article views: 65406 | Views in the last 30 days: 104
Related Articles

SQL Sever Upgrade wizard

Concept of the upgrade wizard


Upgrading Packages to SSIS 2012

Many of you may now or in the future have the need to upgrade your SSIS packages to the new...


SQL Server 2005 SSIS Package Fails After Upgrade to SQL Server 2008

SSIS Package Fails after upgrading Integration Services to SQL Server 2008


Basics of XML and SQL Server, Part 3: Transform and Shred XML in SSIS

This SSIS package performs multiple XSL transformations on an XML document, then shreds the transfor...


Issue While Converting the Import Wizard to Package

Issue While Converting the Import Wizard to Package