SQL Server 2005 Integration Services - Migrating SQL Server 2000 DTS tasks

  • Article

I have extracted the following from a useful article:

SQL Server 2005 Integration Services: Lessons from Project REAL, https://msdn.microsoft.com/sql/default.aspx?pull=/library/en-us/dnsql90/html/SQL05InSrREAL.asp

 

SQL Server 2005 Integration Services is a completely new product. While it is able to continue running SQL Server 2000 DTS packages, SSIS has a new design surface, a new object model, and a new internal design. There isn't necessarily an equivalent or direct upgrade path available for all packages that could be designed in SQL Server 2000 DTS. The Migration Wizard represents a best-effort migration attempt.

 

SQL Server 2000 DTS tasks can be classified into three categories. Your migration experience will depend on which category your tasks fall into.

 

Category 1: Simple Tasks

These tasks are a straight port into SQL Server 2005 Integration Services. They include:

  • Execute SQL task
  • Bulk Insert task
  • File Transfer Protocol task
  • Execute Process task
  • Send Mail task
  • Copy Objects task
  • Execute Package task

If your package only includes these tasks you should have a good migration experience.

 

Category 2: Opaque Tasks

These tasks will migrate, but might not work once they have been migrated. This is especially true of the ActiveX and Dynamic Properties tasks. These tasks typically interact with the SQL Server 2000 DTS object model and the SSIS object model is not backwards-compatible with SQL Server 2000 DTS. Opaque tasks include:

  • ActiveX Script task
  • Dynamic Properties task
  • Analysis Services DTS Processing task

You will almost certainly find that migrating packages with these tasks requires the new development of at least these task components, and may entail a deeper review of your package design.

 

Category 3: Encapsulated Tasks

These tasks are not migrated. When the Migration Wizard is complete, it will have created a new DTS 2000 package that contains these tasks, and then use the Execute DTS 2000 Package task to call into the newly created packages to perform these SQL Server 2000 DTS tasks. They include:

  • Custom tasks
  • Data Pump tasks
  • Data Driven Query task
  • Transform Data task
  • Parallel Data Pump task
  • Copy Database Wizard tasks

For this category of tasks, you must either rewrite the components in SSIS if you want them migrated, or continue to call them using the Execute DTS 2000 Package task.