SQL Server 2005 Integration Services - Working with SQL Server 2000 DTS Packages - Part 27
June 26, 2006
If you have been following this series for at least some time, you should be well aware of important improvements introduced in SQL Server 2005 Integration Services (when compared with Data Transformation Services, which provided similar functionality in earlier version of SQL Server). While superiority of the new technology offers a compelling argument for switching away from its predecessor, the majority of Microsoft customers invested a significant amount of time and resources into designing and deploying DTS-based packages. In this article, we will explore several options that can be used in order to leverage this investment.
Both SQL Server 2005 Management Studio and Business Intelligence Development Studio are capable of handling DTS-based packages and support a variety of functions with their default settings. By selecting the "Connect Object Explorer..." item in the File menu in SQL Server Management Studio and specifying the target server name and authentication information (remember to verify the Network Protocol entry on the Connection Properties tab), you can connect to remote instances to SQL Server 2000. (This ability depends on a number of additional factors, such as network connectivity, compatible client and server network protocol settings, and - in the case of Windows 2003 Server platforms and Windows XP Professional with Service Pack 2 - also on properly configured Windows Firewall restrictions). Once you establish a connection, expand the Management and Legacy folders under the server node, which should reveal the Data Transformation Services subfolder. Its content represents all DTS packages stored in the msdb database on the target server (more specifically, in msdb.dbo.sysdtspackages table). Keep in mind that you cannot access packages stored in Meta Data Services via this interface (but you retain the ability to open .dts Structured Storage files and import them into msdb database, using the Open Package File... item in the context sensitive menu of Data Transformation Services subfolder). Menu options for packages displayed under the Management\Legacy\Data Transformation Services folder hierarchy the Object Explorer window allow you to perform Open, Migrate, Export and Delete actions. While the last one is self-explanatory, the first three warrant additional explanation.
The Open command is intended for editing DTS packages. In order to execute it on upgraded or newly installed instances of SQL Server 2005, you need to have SQL Server 2000 DTS Designer components present on the local computer. Fortunately, these are readily available in the form of a freely downloadable add-in (packaged as SQLServer2005_DTS.msi installation file), which is part of Feature Pack for Microsoft SQL Server 2005. Once the installation is completed, you will be able to edit and save the package with DTS 2000 Package Designer, practically identical to the one available from the Data Transformation Services node in SQL Server 2000 Enterprise Manager (with only minor exceptions, such as, mentioned earlier, a lack of support for enumerating, executing, or modifying packages stored in Metadata Services). Downloadable SQL Server 2000 DTS Designer components are not required in the case of side-by-side installations (or on systems where the SQL Server 2000 Management Tool has already been installed). Upgrades might suffer from undesired side effects, causing the removal of some DTS specific registry entries. Fortunately, these entries can be easily recreated by running the Repair of "Microsoft SQL Server 2005 Backward compatibility" Setup wizard, which is activated by clicking on the Change button next to its entry in the Add or Remove Programs Control Panel applet (note that upgrades do not result in automatic package conversion from DTS to SSIS format).
Once a package is opened in the SQL Server 2000 DTS Designer, you can not only modify it and save your changes to Structured Storage and Visual Basic files or msdb database, but also execute it. The ability to launch legacy packages is implemented in an enhanced version of runtime engine (delivering such new features as access to SSIS data sources). This functionality is part of the already mentioned SQL Server 2005 Backward compatibility software and is applied automatically at the time of installing Integration Services. In addition, you have an option to execute DTS packages from the Command Prompt using the DTSRun.exe utility, which can also be used to copy them from Meta Data Services to a Structured Storage File. If you need to copy DTS package from msdb database to a Structured Storage File, you can take advantage of the Export item from the context sensitive DTS package menu in the Object Explorer of SQL Server Management Studio.
Legacy DTS packages can also be accessed and executed from within the SQL Server 2000 Business Intelligence Development Studio via Execute DTS 2000 Package Control Flow Task. The primary goal of the General section of the task Editor window is to designate the package to be executed. Such package can be stored in SQL Server (this requires that you specify its name and authentication information), Structured Storage File (for which, you need to provide path and name), or it can be Embedded in Task, which improves its portability (you can easily switch to this configuration from the other two by clicking on the Load DTS2000 Package Internally command button). In each case, you need to also select the version of the package to be executed (to fill out the Package Name and ID entries in the Package section of the Task Editor) and, in cases where the package has been encrypted, type in the password.
The techniques described above constitute reasonable workarounds that facilitate modifying and managing legacy DTS packages until you are ready to migrate them into SQL Server Integration Services format. Performing such conversion is the purpose of the Migrate... option in the context sensitive menu of DTS packages listed in the Object Explorer of SQL Server Management Studio. Selecting it triggers the Package Migration Wizard, which also can be launched by running DTSMigrationwizard.exe from its location in Program Files\Microsoft SQL Server\90\DTS\Binn folder or by choosing the Migrate DTS 2000 Package item from the context sensitive menu of the SSIS Packages folder in the Solution Explorer window of Business Intelligence Development Studio. While the wizard features a straightforward interface (it consists of several pages, which prompt you for such information as package source and destination locations and corresponding authentication parameters for each, as well as path and name of the log file, where the migration steps are recorded) and it might complete without generating any errors, its successful outcome is a subject to numerous limitations (regardless of the outcome though, original packages are always left intact). This is due to significant architectural differences between Data Transformation Services and SQL Server 2005 Integration Services, which become evident when dealing with more complex legacy packages. In particular, this applies to tasks, which do not have SSIS equivalents (such as DTS Dynamic Properties Task, whose functionality can potentially be substituted by SSIS-based Package Configurations and property expressions) or referencing DTS object model directly (Active X Script Task). On the other hand, migrating packages employing such tasks as Bulk Insert, Copy SQL Server Objects, Execute SQL, Execute Package, Execute Process, File Transfer Protocol, Send Mail, is likely to result in success eliminating the need for manual modifications. Wizard tends to deal with more complex or custom transforms and tasks by incorporating them into the earlier described Execute DTS 2000 Package Task (which you can subsequently review and attempt to convert into SSIS specific format on your own). This typically applies to Analysis Services, Parallel Data Pump, or Data Driven Query tasks. In addition, features such as package passwords, logging and error handling configuration, transaction settings, as well as text annotations are not migrated. Microsoft documented a number of Known Package Migration Issues (along with corrective actions) and included them in SQL Server 2005 Books Online.
You can also get a general idea of what degree of success to expect by running Microsoft SQL Server 2005 Upgrade Advisor, freely downloadable from the Microsoft Download Center. Note that while this software is capable of interacting with remote systems, it has a number of SQL Server specific requirements (more specifically, SQL Server 2000 Analysis Services Decision Support Objects and SQL Server 2000 Client Components for scanning DTS packages), in addition to more generic ones, regarding the operating system platform (Windows 2000 SP1 or later, Windows XP Professional SP1, or Windows 2003 Server SP1), as well as versions of .NET Framework (2.0) and Windows Installer (3.1). The Analysis Wizard module of the Advisor examines legacy instances of SQL Server (7.0 and 2000 versions) covering such functionality as Analysis Services, Notification Services, Reporting Services, and Data Transformation Services (both msdb and structured files-based packages). For each of them, identified issues can be presented in the report form by the Report Viewer module, including a list of affected objects. Following the migration, you should always validate the results by reviewing the new version of the package.