Migration DTS packages to SSIS 2005/2008


Setup SSIS with DTS 2000

On the migration of 2000 DTS to SSIS 2005/2008. There are many known issues in the migration of DTS 2000. It is recommended to execute the DTS inside SSIS 2005/2008 without migration. And if you need to edit a DTS in 2000 it will be better to recreate from scratch.

Known issues related to migration packages
SQL Server 2005 : http://msdn.microsoft.com/en-us/library/ms143462(SQL.90).aspx 
SQL Server 2008 : http://msdn.microsoft.com/en-us/library/ms143462.aspx

Support for Data Transformation Services (DTS) in SQL Server 2008
http://msdn.microsoft.com/en-us/library/bb500440.aspx


What should I install for SSIS developers

You will find the answers in the documentation below:

Installing SQL Server Integration Services by Using Setup
SQL Server 2005 : http://msdn.microsoft.com/en-us/library/ms143510(SQL.90).aspx 
SQL Server 2008 : http://msdn.microsoft.com/en-us/library/ms143510.aspx

"When you select Integration Services for installation, Setup also installs support for SQL Server 2000 Data Transformation Services (DTS) packages, including the DTS runtime and DTS package enumeration in SQL Server Management Studio. Run-time support has been updated to allow DTS packages to access SQL Server 2005 data sources. If you are not installing Integration Services, but you need support for DTS packages, then you need to make sure that Legacy Components is selected on the Feature Selection page."

There are some interesting components to you:

Microsoft SQL Server 2000 DTS Designer Components
SQL Server 2005 : http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=fr 

How to: Install Support for Data Transformation Services Packages
http://msdn.microsoft.com/en-us/library/ms143755.aspx  

Microsoft SQL Server 2005 Backward Compatibility Components
SQL Server 2005 : http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=fr
SQL Server 2008 : http://www.microsoft.com/downloads/details.aspx?FamilyId=C6C3E9EF-BA29-4A43-8D69-A2BED18FE73C&displaylang=fr


Sample

With SQL Server Management Studio 2005/2008

Import the package DTS 2000
in SQL Instance >> Management >> Legacy >> DTS : Import

Migrate the package DTS 2000 to SSIS
package Instance >> Management >> Legacy >> DTS : Migrate wizard




Michel Degremont | Microsoft EMEA
Product Support Services Developer - SQL Server Core Engineer |