Programming Samples

Click here to go to:



Excel VBA

Word VBA

MS Access

T-SQL

SSIS

SSRS

Power BI

Crystal Reports

SSAS

SQL Replication

C# Code

ASP .NET Code

Oracle PL/SQL

Database Diagramming


Back to Home Page


SQL Server DTS Package Migration

Migrating DTS Packages to SSIS

A tutorial on how to migrate SQL Server 2000 DTS Packages to SSIS in SQL Server 2005\2008.

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.

SSMS view

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).

Pkg Start

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.

Source Location

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

SSIS Destination Location

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

SSIS List Packages

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

SSIS log file

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

SSIS Package Migration

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.

SSIS BIDS

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

SSIS Package Copy Add

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

SSIS Solution Explorer

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

SSIS DTSX Package Design View

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