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.
Agile Methodology in Project Management
In this video, you will gain an understanding of Agile and Scrum Master Certification terminologies and concepts to help you make better decisions in your Project Management capabilities. Whether you’re a developer looking to obtain an Agile or Scrum Master Certification, or you’re a Project Manager/Product Owner who is attempting to get your product or … Continue reading Agile Methodology in Project Management
Creating Users and Managing Passwords in Microsoft Office 365
In this Office 365 training video, instructor Spike Xavier demonstrates how to create users and manage passwords in Office 365. For instructor-led Office 365 training classes, see our course schedulle: Spike Xavier SharePoint Instructor – Interface Technical Training Phoenix, AZ 20347: Enabling and Managing Office 365
Detailed Forensic Investigation of Malware Infections – April 21, 2015
How does an investigator hunt down and identify unknown malware? In this recording of our IT Security training webinar on April 21, 2015, Security expert Mike Danseglio (CISSP / CEH) performed several malware investigations on infected computers and identify symptoms, find root cause, and follow the leads to determine what’s happening. He demonstrated his preferred … Continue reading Detailed Forensic Investigation of Malware Infections – April 21, 2015
Write a Comment
