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

Home > Blogs > SQL Server > Moving DTS packages to a SQL Server 2005 Server by Jeff Jones

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

Like This Blog 0 Jeff Jones
Added by 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.

Videos You May Like

A Simple Introduction to Cisco CML2

0 3661 0

Mark Jacob, Cisco Instructor, presents an introduction to Cisco Modeling Labs 2.0 or CML2.0, an upgrade to Cisco’s VIRL Personal Edition. Mark demonstrates Terminal Emulator access to console, as well as console access from within the CML2.0 product. Hello, I’m Mark Jacob, a Cisco Instructor and Network Instructor at Interface Technical Training. I’ve been using … Continue reading A Simple Introduction to Cisco CML2

Configuring Windows Mobility Center and How to Turn it On and Off

1 1404 1

Video transcription Steve Fullmer: In our Windows training courses, we often share information about the Windows 8.1 Mobility Center. Mobility Center was introduced for mobile and laptop devices in Windows 7. It’s present and somewhat enhanced in Windows 8. Since we don’t have mobile devices in our classrooms, I decided to take a little bit … Continue reading Configuring Windows Mobility Center and How to Turn it On and Off

OSPF Adjacency Troubleshooting Solution – Getting Close to the OSPF adj

0 246 1

In this video, Cisco CCNA & CCNP instructor Mark Jacob shows how to troubleshoot OSPF Adjacency issues by showing the distance between routers with the show ip ospf neighbor command.

Write a Comment

Share your thoughts...

Please fill out the comment form below to post a reply.