Can we run a legacy DTS package written for and currently running on an instance of SQL Server 2000 on a server running SQL Server 2008 R2?
We have a database that we know will run fine on SQL Server 2008 R2 but the legacy DTS package is causing us concern in migrating. We would even consider an alternative that included loading components of SQL Server 2000 just to have the ability to run this DTS package.
From the MSDN article Migrating DTS Packages to Integration Services
Support for DTS in SQL Server 2008
DTS packages can still run as before when you have the SQL Server 2008 database engine installed. To accomplish this, you must have the Microsoft SQL Server Backward Compatibility feature installed from the SQL Server installation media or the Feature Pack Web page. This installs the DTS runtime for SQL Server 2008, which works on 32-bit platforms only.
If you perfom an in-place upgrade from SQL Server 2000 to SQL Server 2008, then all the DTS packages stored locally will be preserved as DTS packages in SQL Server 2008. The upgrade process does not automatically migrate DTS packages to the Integration Services format. To edit the DTS packages, you must have the Microsoft SQL Server 2000 DTS Designer Component installed, which is part of the SQL Server 2005 Feature Pack and is a separate download from the Microsoft website.
You can manage your DTS packages from SQL Server Management Studio under the Management à Legacy à Data Transformation Services node. Here, you can open existing DTS packages stored on the file system or in the msdb database, or add additional packages to the server by clicking the Import button. New DTS packages cannot be created from Management Studio, but old packages can be modified and renamed.
