SQLServerCentral Article

Migrating SQL Server 2000 DTS Packages Across Environments

,

Migrating the developed DTS Packages across environments is an inherit and implicit activity that is required in all database projects as a part of code deployment and release activities. In repetitive mode of deployments there will be cases when some DTS packages are modified and the rest remain the same. These changed packages need to be migrated to another environment.

The pain point in such migration activities is the cyclic and repetitive nature of work. This is more of an issue when the source and target servers are not within the same network and a direct network link is not possible between the servers.

If network connectivity was not the problem then the stored procedure sp_OACreate could be employed to do the job. However in many cases, this might not be feasible. Besides the network connection between the source and the target, there are some other issues pertinent of mention when considering migration. The most evident of it is the "version issue". A DTS package can have multiple versions. This fact combined with the possibility of calling a package from within another package can create issues when migrating a limited set of packages and when the migration of entire DTS package set is too time consuming. E.g. suppose initial development demanded creation of packages PACK1(Version 1) and PACK2(Version 1). Then we bundled these two into another package PACK3.

Later we update the package PACK1, so it gets a new version Version 2. However PACK3, unless embedded package PACK1 is not re-referenced, is referencing an older version of PACK1. So migrating just the Package PACK1 is not sufficient. You need to update all the dependent packages, either manually or otherwise to point to the latest version of package – PACK1 (Version 2)

Another activity that needs to be done after migrating the packages is updating the username/password and server details of connection objects. Doing this manually and for a dozen odd packages every alternate day can run you out of patience.

Thankfully SQL-DMO and DTS Object model can overcome these issues.

The idea is to employ the DTS Object Model library of Visual studio and use its classes, methods and properties to

  1. Download the latest version of packages from source environments in form of Structured Storage Files(.dts) and
  2. Import these structured files to the target database and do the following

    • Reset the connection properties.
    • Reset the version of all embedded packages to the latest one.

This article presents a tool created using DTS Object Model and VB to do the basic migration. Even if there might be complex scenarios in DTS steps, this tool can do the bulk work and some manual intervention can complete the migration, instead of doing all the work manually.

Steps to make use of the tool

  1. Place the ExtractDTSVBApp.exe and ImportDTSVBApp.exe in a folder.
  2. Place the ImportDTS.bat and ExportDTS.bat in another/same folder.
  3. Edit the ImportDTS.bat and ExportDTS.bat and replace the occurrence of ExtractDTSVBApp.exe and ImportDTSVBApp.exe with the correct folder location of these exes.
  4. Create a folder C:\DTS in the source and target servers. On the source, a log(DTSMigrate.log), a list of dts packages extracted (DTSFileListMigrate.txt) and one .dts file per dts package is created after executing the ExtractDTS. On the target you need to ship the list of packages file(DTSFileListMigrate.txt) and all the .dts files you need to migrate. You can also delete from DTSFileListMigrate.txt the dts files that you don't want to import on target. The ImportDTS will look for the files listed in the DTSFileListMigrate.txt and import them with updated reference on the target.
  5. Use ImportDTS –HELP and ExportDTS –HELP to see options.
  6. SQL-DMO, VB Runtime and DTS object library should be installed.

The code listing below shows two modules – One for Export and another one for import

Export or Extract of Latest DTS packages:

A) Batch Script(ExtractDTS.bat)

B) VB Main module: (mainVB.txt)

Import DTS packages:

A)Import script (import.txt)

B)VB Main module: (VB MAin 2)

Example Workflow:

1. Source Database Server with three packages. Embedded calls DTSPack1 and DTSPack2

2. C:\DTS folder created. The Extract DTS –Help specifies option.

3. Execute ExtractDTS ServerName UserName Password. Creates the log, package list and the individual DTS packages in C:\DTS

4. Target server doesn't have any package.

3. Copy the files from Source to Target

  • DTSFileListMigrate.txt
  • DTSpack1.dts
  • DTSpack2.dts
  • Embedded.dts
  • Exes
  • Batch files as in indicated in the section (Steps to make use of the tool)
  • See help on ImportDTS by typing

    ImportDTS -help
    

4. Execute the command: "ImportDTS TargetServerName Username Password"

5. Packages imported in the Target Server

6. Execute the embedded Package on target.

Scope for further development:

The tool functionality can be broadened to customize and generalize the migration. Some of ideas are:

  1. Create a mapping file (source connection:target connection) and then replace all the data sources in the dts packages on target server with respective target connection settings.
  2. Ability to filter out DTS export and import based on certain criteria like name matching or regular expression checking.
  3. More error logging and handling.

You are free to use the code and improve on it.

NOTE: NO GUARANTEE OF ANY SORT IS UNDERTAKEN FOR THE USAGE OF THE CODE OR PROGRAM. PLEASE TEST THE CODE IN YOUR ENVIRONMENT BEFORE USING OR MODIFIYING IT.

Resources

Rate

3.13 (8)

You rated this post out of 5. Change rating

Share

Share

Rate

3.13 (8)

You rated this post out of 5. Change rating