Migrating Legacy DTS packages

The environment: SQL Server 2000, SQL Server 2005 and SQL Server 2008

Consider an environment in which you're asked to migrate a large number of DTS packages from one of your servers onto another server.  You could manually move each package, but if the following conditions are met, a simple insert statement into msdb..sysdtspackages can make your life a lot easier:

• you don't need to convert your DTS packages to SSIS packages
• all of your packages are stored on the server (rather than as a file)
  

use [msdb]

go

insert into sysdtspackages

    select [name],
        id,
        versionid,
        description,
        categoryid,
        createdate,
        owner,
        packagedata,
        owner_sid,
        packagetype
    from [linkedserver].msdb.dbo.sysdtspackages