Migrating DTS (Data Transformation Services) packages across environments is a common task in database projects. It is a crucial part of code deployment and release activities. However, this process can become repetitive and time-consuming, especially when only a few packages have been modified while the rest remain the same.
One of the challenges in migrating DTS packages is the network connectivity between the source and target servers. If a direct network link is not possible, the traditional approach of using the stored procedure sp_OACreate may not be feasible. Additionally, there are other issues to consider, such as versioning and dependency management.
A DTS package can have multiple versions, and if a package calls another package, it can create issues during migration. For example, if we have a package called PACK3 that includes PACK1 (Version 1) and PACK2 (Version 1), and later we update PACK1 to Version 2, PACK3 will still reference the older version of PACK1. Therefore, simply migrating PACK1 is not sufficient; we need to update all dependent packages to point to the latest version of PACK1 (Version 2).
Another task after migrating the packages is updating the connection properties, including the username, password, and server details. Doing this manually for multiple packages can be tedious and time-consuming.
Thankfully, SQL-DMO (SQL Distributed Management Objects) and the DTS Object Model can help overcome these challenges. By using the DTS Object Model library in Visual Studio, we can download the latest version of packages from the source environment as Structured Storage Files (.dts) and import them into the target database. This approach allows us to reset the connection properties and update the version of all embedded packages to the latest one.
In this article, we present a tool created using the DTS Object Model and VB (Visual Basic) to simplify the migration process. While there may be complex scenarios in DTS steps, this tool can handle the bulk of the work, reducing the need for manual intervention.
Steps to Use the Tool:
- Place the ExtractDTSVBApp.exe and ImportDTSVBApp.exe in a folder.
- Place the ImportDTS.bat and ExportDTS.bat in another folder (can be the same folder).
- Edit the ImportDTS.bat and ExportDTS.bat files and replace the occurrence of ExtractDTSVBApp.exe and ImportDTSVBApp.exe with the correct folder location of these executables.
- Create a folder named C:\DTS on both the source and target servers.
- On the source server, execute the ExtractDTS.bat file, which will create a log file (DTSMigrate.log), a list of DTS packages extracted (DTSFileListMigrate.txt), and one .dts file per DTS package in the C:\DTS folder.
- On the target server, copy the DTSFileListMigrate.txt file and all the .dts files that need to be migrated.
- Optionally, you can delete the .dts files from the DTSFileListMigrate.txt file that you don’t want to import on the target server.
- Execute the ImportDTS.bat file on the target server, specifying the target server name, username, and password as parameters.
- The tool will import the packages listed in the DTSFileListMigrate.txt file to the target server with updated references.
It is important to note that SQL-DMO, VB Runtime, and the DTS object library should be installed on the servers for the tool to work properly.
The tool provided in this article is a starting point and can be further developed to customize and generalize the migration process. Some ideas for improvement include:
- Creating a mapping file to replace all data sources in the DTS packages on the target server with the respective target connection settings.
- Adding the ability to filter DTS export and import based on certain criteria, such as name matching or regular expression checking.
- Enhancing error logging and handling for better troubleshooting.
Feel free to use the code provided and improve upon it. However, please note that there is no guarantee for the usage of the code or program. It is recommended to test the code in your environment before using or modifying it.