Let's assume your company wants to upgrade from SQL Server 2000 to SQL Server 2005. There are many reasons to justify this decision as outlined in the following excellent white paper: Why upgrade to SQL Server 2005.
Requires Free Membership to View
What if your company has hundreds of SQL Server 2000 DTS packages in their environment that took years to design, deploy and stabilize? Further, what if you know these are complex packages that, for reasons discussed later in this tip, will not be easily upgraded from DTS to SSIS (SQL Server Integration Services)? Do you put off database upgrade plans because the daunting DTS-to-SSIS migration task is something that they are not ready to undertake?
This article looks at a way you can edit, maintain and even continue developing your existing SQL 2000 DTS packages using the SQL Server 2000 DTS Designer Tool until these DTS packages can be upgraded to SSIS packages. We will see how this design tool works within a SQL 2005 environment. We will discuss some typical DTS to SSIS migration challenges and we will see how these 'challenging packages' can be identified. Finally, we will look at a SQL Server 2000 to 2005 upgrade path for our sample company and provide the rationale for choosing to preserve our DTS environment.
The DTS to SSIS migration challenges
Why would migrating a DTS Package to SSIS pose such a challenge and why would we even need to use
the SQL Server 2000 DTS Designer? To answer to this question, one only has to compare DTS to SSIS.
SSIS is a true enterprise-level ETL platform and while it was designed based on ideas developed in
DTS, it is a wholly different product. It follows that there are DTS components that are simply not
compatible with or do not map directly to SSIS components. These incompatible components include:
Data Driven Tasks, ActiveX Script tasks that access the DTS Object Model, Dynamic Property tasks,
global variables and custom tasks, just to name a few. (Microsoft SQL Server 2005 Integration
Services by Kirk Haselden contains an entire chapter on migrating from DTS and discusses these
components along with other SSIS migration issues in detail.) How do you know if your DTS packages
contain these components? Fortunately, there is a very useful freely downloadable tool called the
SQL Server 2005 Upgrade Advisor that can point these migration challenges out and therefore help
gauge your migration effort.
The SQL Server 2005 Upgrade Advisor
Before performing an upgrade to SQL 2005, it's highly recommended that you utilize the Microsoft
SQL Server 2005 Upgrade Advisor to scan not only your databases, but also your DTS Packages.
The Upgrade Advisor doesn't make any changes. It scans and identifies feature and configuration
changes that might affect your upgrade. The Upgrade Advisor then provides links to documentation
that describes each identified issue and how to resolve it. You can use this tool to help scope out
your DTS to SSIS migration effort.
We will assume that the Upgrade Advisor pointed out our migration challenges for our customer's DTS packages. While our DTS packages may contain many incompatible components, a migration path still exists for these targeted packages. The migration path may require some timelier package re-engineering, as opposed to a simple wizard-driven DTS to SSIS migration. In the interim, we have made our choice to support our current DTS environment using the SQL Server 2000 DTS Designer tool discussed in our next section. Keep in mind that we have chosen to continue with our database migration because the Upgrade Advisor did not find migration issues with our databases. Let's take a more detailed look at our SQL Server 2000 DTS Designer.
The SQL Server 2000 DTS Designer
The SQL Server 2000 DTS Designer allows you to maintain and even modify your DTS packages from SQL
2005's Management Studio. (You install it on your SQL Server 2005 server.) The package development
environment very closely mimics the DTS Package development environment you may be used to using in
SQL Server 2000's Enterprise Manager. This DTS Designer is part of the freely downloadable SQL
Server 2005 Feature Pack and provides you with the ability to edit and save your DTS packages
from a SQL Server 2005 environment.
While the SQL Server 2000 Designer is for maintaining and developing DTS packages, the Business Intelligence Development Studio is used to develop and maintain SSIS packages. Since we have made our choice to perform the upgrade of our SQL Server databases and to preserve our extensive and complex SQL 2000 DTS environment, let's take a look at our proposed upgrade plan.
Our SQL 2005 in-place upgrade removes SQL 2000
One site I upgraded from SQL 2000 to SQL Server 2005 chose to run both versions of SQL Server, SQL
2000 and SQL 2005, side-by-side. (Several of their third-party applications were not ready to
support SQL 2005). Doing this was both practical and possible because they were a low-volume shop
with very small-scale databases. By performing a side-by-side upgrade, we were actually able to
preserve our SQL Server 2000 development environment so we didn't need to download the 2000 DTS
Designer. In many cases, this type of upgrade may not be practical or even possible. Instead, an
in-place upgrade may be a preferred option. An in-place upgrade essentially overwrites your SQL
2000 instance with a 2005 instance. If you choose this upgrade path, your databases are replaced
with their 2005 equivalents and your development tools are replaced with 2005's new tools. We will
assume this is the case with our large-scale high-volume customer and that we will no longer have
the SQL 2000 designer tools such as Enterprise Manager at our disposal once the upgrade is
complete.
Our post-upgrade state of the union: Where did our old DTS packages go?
After you perform an in-place upgrade of SQL Server 2000 to SQL Server 2005, your DTS Packages are
NOT automatically upgraded. Instead, your non-upgraded DTS packages and jobs are carried over to
the SQL 2005 instance in place. You will be able to find these 2000 packages in SQL Server 2005
Management Studio under Management > Legacy > Data Transformation Services. You can
continue to run these packages along with the jobs that kick-start them since the DTS runtime
environment carries over; however, you will not be able to modify these packages without installing
the SQL 2000 DTS Designer. Assuming you installed the 2000 DTS Designer, you can open and then
modify your packages by right-clicking on them and choosing Open.
Conclusions
Long-term support for SQL Server 2000 DTS may not be in cards; therefore, it may be wise to
consider using this release of SQL Server 2005 to upgrade your DTS Packages to SSIS. The SQL Server
2000 DTS Designer can be used to give you the time you may need to meticulously plan for the
eventuality of a DTS to SSIS upgrade project.
When you are ready to upgrade your DTS packages, you can use the Package Migration Wizard which can be launched from either Management Studio or the Business Intelligence Development Studio. The Migration Wizard performs a "best effort" migration, meaning it migrates the components that have DTS to SSIS mappings. For the components that cannot be mapped, the wizard encapsulates these tasks or features in an Execute DTS 2000 Package Task. These legacy remnants should be the tasks that you focus on re-engineering to take advantage of SSIS's rich set of features such as Containers or Package Configurations. In many cases, tasks that required custom code under DTS have pre-baked existing feature support in SSIS. Here, knowledge of SSIS's stand-out features and capabilities will prove invaluable.
Caveats
The SQL Server 2005 Upgrade Advisor has a number of SQL Server specific requirements, operating
system and service pack requirements, .NET framework and Windows Installer requirements.
The SQL Server 2005 Upgrade Advisor and the Package Migration Wizard can only scan DTS packages that reside in the Repository if the SQL Server 2000 tools are installed on the local computer. SQL Server 2005 does not install or use the Repository.
When you select Integration Services for installation, setup also installs support for DTS packages including the DTS Runtime. If you upgrade the last instance of SQL Server 2000 on the computer to SQL Server 2005, registry entries are removed that are required by DTS. These can easily be restored by running the Repair of "Microsoft SQL Server 2005 Backward Compatibility" Setup wizard.
This article also appears at RDA Spotlight on
Business and Technology.
ABOUT THE AUTHOR
Joe Toscano of RDA is a SQL Server database
consultant, instructor and speaker based near Philadelphia, Pa. He has worked extensively with SQL
Server since version 6.5, as well as Sybase and Informix prior to that. Joe's areas of expertise
include Data Transformation Services (DTS), Integration Services (SSIS), Transact-SQL (T-SQL)
programming, data warehousing, performance tuning, replication, high availability (mirroring),
security, disaster recovery, upgrading, migration projects and lights-out DBA tasks.
This was first published in April 2007
Join the conversationComment
Share
Comments
Results
Contribute to the conversation