SQL Server 2005 Integration Services – Working with SQL Server 2000 DTS Packages – Part 27

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 ManagementLegacyData 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
FilesMicrosoft SQL Server90DTSBinn 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.

»


See All Articles by Columnist
Marcin Policht

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles