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
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
In
January, my new book comes out on SQL Server 2005 Integration Services from Wrox.
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
