How to migrate DTS packages (to DTS/SSIS packages)

Hi,

Currently we are investigating ways how to migrate DTS packages from one server to another. Because of old hardware we need to migrate the DTS packages from the old system to a new system as soon as possible. Thereore we decided to move the DTS packages with minimal adjustments to the new servers.We decided to use the legacy mode of SQL Server 2008.

As i previously blogged,  first we investigated the current situation with following methods:

• Gather information about the databases, DTS packages and jobs.
• Gather information about the content of a DTS packages with DTSDOC.
• Script the jobs of the servers.
• Gather information about the database objects (tables, views, stored procedures, etc) with DBDESC.
• We created a DTS/database matrix. This way we could investigate the impact of moving a database to a another server.
• We also created a Job/database matrix. A lot T-SQL code was embedded in jobs.

We will use this knowledge of the DTS packages and others when we will built the packages into SSIS packages later on in the project.

When analyzing how to movedatabases and DTS packages to another server we discovered that there were 4 ways of doing this:

You could replace a database with a text file or whatever. This is the same way of migration of a database. Also when a DTS package has a stored procedure: this would be the DTS migration. It will point to the old database. You could also use one of these scenarios when migrating to SSIS packages (as we will do later in the project).

If i figure out more examples of migrating DTS packages, i let you know.

Greetz,

Hennie