One of the more interesting and perhaps challenging aspects of a SQL Server 2000 to SQL Server 2005 upgrade is the migration from DTS to SSIS for ETL (Extract-Transform-Load) activities. SSIS isn’t an upgrade of DTS, but is in fact an entirely new, and greatly enhanced, component. While it’s possible to run DTS packages under SSIS, SSIS offers so many new features and capabilities that it’s well worth your while to take the plunge.
If you don’t have the resources to convert your old packages, you can at least build your new ETL tasks in SSIS. The deeper you get into SSIS, though, the more you’ll want to convert your entire SQL Server 2005 ETL workload to this tool.
The SQL team has provided a series of ten “Guided Tour” videos (.wmv format) to help shepherd you through this transition. Topics include:
SQL Server Integration Services Guided Tour
Heterogeneous Data Connectivity Guided Tour
Scalability & Parallel Task Execution Guided Tour
Data Warehouse Smarts Guided Tour
Metadata Management Guided Tour
Visual Development Environment Guided Tour
Manageability and Deployment Guided Tour
Data Quality Guided Tour
Custom Scripting Guided Tour
Custom Components Guided Tour
These are great introductions to the power of SSIS, and will be of great assistance to you as to navigate the DTS -> SSIS transition.
(updated 10 March 2007 to “translate” ETL correctly (thanks, Ken Tanner))