Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Migrating SQL Server 2000 DTS Packages Across Environments

By Owais Bashir Ahmed,

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:

mainVB.txt | mainVB2.txt
Total article views: 6084 | Views in the last 30 days: 13
 
Related Articles
FORUM

SQL code for selecting source column names and target column names that are mapped and writing into a log file

I have a package where source table and target table are mapped. I want a log file to see to what ta...

FORUM

SSIS-packages-version

to identify the version on packages.

FORUM

DTS sql 2000 package migration issue

DTS package migration issue

FORUM

Migrating DTS Packages to SSIS

Migrating DTS Packages to SSIS

ARTICLE

Version Control - Part 3 - Migrating Objects to Production

Part 3 of Steve Jones' series on version control. This article examines how migrate your changes to ...

Tags
dts    
sql server 7    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones