Moving DTS packages to a SQL Server 2005 Server by Jeff Jones
Moving DTS packages to a SQL Server 2005 Server by Jeff Jones
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.
You May Also Like
DBA, dbo, DTS, Integration Services, MSDB, OLE DB, SQL, SQL Server 2000, SQL Server 2005, sysdtspackages
A Simple Introduction to Cisco CML2
0 3852 0Mark 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
Cable Testers and How to Use them in Network Environments
0 713 1This 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
Government Edition – Encrypting a USB Flash Drive in Windows 10
0 272 2In this video, Security Instructor Mike Danseglio demonstrates how to use BitLocker in Window 10 to secure files on a USB Flash drive that adhere to stricter data protection requirements as found inside Government entities. BitLocker 2-day instructor-led training is now available at Interface: BITLOCK: Planning and Deploying BitLocker Drive Encryption Training Video Transcription: Hi. … Continue reading Government Edition – Encrypting a USB Flash Drive in Windows 10