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.
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.
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.
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.