I’ve seen this approach from the CSS SQL Server Engineers blog post and wondered if I can give it a shot. We’re migrating a SQL Server 2000 database to a SQL Server 2005 instance (finally!) and that included a lot of DTS packages. While I would highly recommend upgrading the DTS packages to SSIS packages for the long term due to product support lifecycle reasons, that would take quite a while and would impact businesses dramatically.

I’ve used Method 2A mentioned in the blog post to move the DTS packages from the SQL Server 2000 to SQL Server 2005 as I have like more than a hundred DTS packages stored in MSDB. Remember that the size of the sysdtspackages table in MSDB will depend on not just the number of packages stored but the number of versions of the packages you decide to keep.

After a restore of the MSDB database from the SQL Server 2000 instance and importing the records from the sysdtspackages table, you would definitely want to install the Microsoft SQL Server 2000 DTS Designer Components to modify and edit your DTS packages in SQL Server 2005 to change those connection strings, test them after migration and so on.