SQLServerCentral Article

Upgrading SQL Server 2000 DTS Packages to SSIS

,

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

Rate

3.29 (14)

Share

Share

Rate

3.29 (14)