Moving DTS packages to a SQL Server 2005 Server by Jeff Jones

Like This Blog 0

Added by Jeff Jones October 20, 2006

I have a bunch of DTS packages sitting in the MSDB database on my SQL Server 2000 system. I want to move the packages to my SQL Server 2005 server to continue executing them while I begin the migration. It looks like I need to generate a .dts file from the SQL 2000 MSDB and then import them one at a time into SQL Server 2005's MSDB.

Well, there is a better way. You can use Integration Services to move them. In SQL Server 2000 the packages are stored in the table dbo.sysdtspackages in MSDB. This table is exactly the same as dbo.sysdtspackages in SQL Server 2005. This table is where the SQL Server 2005 upgrade process places DTS packages from SQL Server 2000. Integration Services packages are placed in the table dbo.sysdtspackages90.

So all we have to do is build a very simple package that copies the rows from the dbo.sysdtspackages in SQL Server 2000 to SQL Server 2005. Define a new Integrations Services package, add a data flow to it. Then add an OLE DB source adapter that points to your SQL Server 2000 MSDB database. If you want to copy all packages and all their versions, include the query "SELECT * FROM dbo.sysdtspackages". If you want just the latest package versions you can use the following query:

SELECT t1.*
FROM dbo.sysdtspackages as t1
       INNER JOIN (SELECT [name]
                   , [id]
                   , MAX([createdate]) as [createdate]
                  FROM dbo.sysdtspackages
                  GROUP BY [name], [id]) AS t2
     ON t1.[id] = t2.[id]
          AND t1.[createdate] = t2.[createdate]

The OLE DB Source Adapter connects directly to an OLE DB Destination Adapter mapping all the columns across. The Destination Adapter must connect to your SQL Server 2005 MSDB database. After defining the connection you will try to select the dbo.sysdtspackages table in the destination but it will not show up in the drop down list. I found you need to define a variable at the package level using the string data type and provide a value of "dbo.sysdtspackages". Then go back to your destination's Data Access Mode drop down list and select "Table name and view name variable". This will activate the Variable name drop down list. Select the variable you just created with the dbo.sysdtspackages value.

Run this package and it will copy those packages from SQL Server 2000 to SQL Server 2005. They will show up in the Management/Legacy/Data Transformations Services folder in SQL Server 2005 Management Studio. To view and edit the DTS packages download the Microsoft SQL Server 2000 DTS Designer Components here Microsoft SQL Server 2000 DTS Designer Components.