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 3877 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

Creating Dynamic DNS in Network Environments

0 641 1

This content is from our CompTIA Network + Video Certification Training Course. Start training today! In this video, CompTIA Network + instructor Rick Trader teaches how to create Dynamic DNS zones in Network Environments. Video Transcription: Now that we’ve installed DNS, we’ve created our DNS zones, the next step is now, how do we produce those … Continue reading Creating Dynamic DNS in Network Environments

Cable Testers and How to Use them in Network Environments

0 724 1

This content is from our CompTIA Network + Video Certification Training Course. Start training today! In this video, CompTIA Network + instructor Rick Trader demonstrates how to use cable testers in network environments. Let’s look at some tools that we can use to test our different cables in our environment. Cable Testers Properly Wired Connectivity … Continue reading Cable Testers and How to Use them in Network Environments

Write a Comment

Share your thoughts...

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