DTS Package Migration Background

If you have migrated to Windows 7 and SQL Server 2008, you may find that you no longer have Runtime access to your DTS Packages from SQL Server 2000 under the Legacy, Data Transformation Services in SSMS. There is a way to get runtime access to these packages, but it requires making the jump to SSIS. The following details describe the first steps in getting the packages migrated.

Assuming that you have Exported the .dts packages to a network drive, they can easily be imported to SQL Server 2008 as Legacy items. The following 2 msi packages must be installed: SQLServer2005_DTS.msi (DTS Designer Components), SQLServer2005_BC.msi (SQL Server Backward Compatibility). I found that the copy on my disk for the Backward Compatibility was older than the Internet version available. The 2 msi files are available here.

To import the DTS package, open SSMS and right click on the Management, Legacy, Data Transformation Service node and selected Import to insert the .dts packages that are on the network or local hard drive.

I found that I was still unable to view the DTS packages from SSMS after installing them, however, I was able to Migrate them to SSIS in the following steps:

DTS Package Migration via the Wizard

On the package to upgrade to SSIS, right click on the package and select Migrate or right click on the Data Transformation Services folder and select Migration Wizard (to retrieve the .dts file from the hard drive).

The Package Migration Wizard will start and prompt for the location of the dts package. Select SQL server if it is on the server or Structured Storage File if it is on the hard drive.

Select either DTSX file or SQL Server for the Destination. Specify the folder name if it is a DTSX file on the hard drive.

Click the checkbox next to the Source Package and click on the Destination Package, if you wish to rename it. Click next to continue.

Specify a log file, in case something goes wrong with the migration. This is an optional step. Click Next.

The package will be migrated to SSIS. Click Close when it is finished.

Open Visual Studio and select Business Intelligent Projects (assumes BIDS is installed on the computer) and create a New Project. Right click SSIS Packages in the Solution Explorer and select Add Existing Package.

The Add Copy of Existing Package window will popup. Select the Package Location (File System, SQL Server or SSIS Package Store).

Once the package has been added to the project, double click on it to open it in the Designer.

From the Designer, each task and connection may be edited.

Migrating to SSIS may seem to be a difficult job, but once complete, the DTSX jobs will be ready for deployment on a SQL 2005 or 2008 Server