I wrote this post months ago but didn’t get around to publish it. I know that SQL Server 2008 is now an available product but many of us are still using SQL Server 2005 and have created a lot Data Transformation Services (DTS) in the past.
In SQL Server 2005, Microsoft replaced DTS with SQL Server Integration Services (SSIS). The common work flow IDE that I am familiar with in SQL Server 2000 is now called SQL Server Business Intelligence Development Studio. It is a Visual Studio 2005 development environment which allows the creation of Analysis projects, Integration Services projects and Reporting Services projects.
However, all is not lost for SQL Server 2000 DTS packages. SQL Server 2005 provides backward compatibility. You can import existing DTS packages into SQL Server 2005 and run them like how you would in SQL Server 2000. If you look at the SQL Server Management Studio (the replacement for Enterprise Manager), you will see that SQL Server 2005 provides legacy support for Maintenance Plans, DTS and SQL Mail.
If SQL Server 2005 supports legacy DTS then it means you can edit and make changes to existing DTS packages in SQL Server 2005. To do that you have to download SQL Server 2000 DTS Designer Components and install it first.
The DTS Designer Component has the familiar DTS interface you see in SQL Server 2000. You can edit, run now or schedule the DTS to run as a scheduled job using the DTS Designer Component. Migration of DTS to SSIS project is also catered for. SQL 2005 provides a migration wizard. Since the design of DTS is very different from SSIS, migration may not always be 100% successful. Chances are the migration may not migrate all tasks successfully. For tasks that fail to migrate properly, the suggestion is to create them in SSIS using the SSIS objects.
a tech junkie and a software developer.
a apple fan and an avid photographer.
a frequent traveller and loves art and graphic novels.
