Solved

Copying DTS packages btn sql 2000 servers

Posted on 2011-03-11
8
236 Views
Last Modified: 2012-06-22
Hi Guys,

It might sound simple but as its new to me..am bit unsure on doing it without flaws.
The support team did asked me to copy of abt 27 DTS packages from one of the sql 2000 server to another sql 2000 server.
Hmm after a bit of google help, I resorted to do it this way:
Right click on each package--> design package-->select save as option-->structural form and in a folder over the source server-->copied it on to the target sql 2000 server --completed till here...

Again as per info available Im supposed to open enterprise manager-->right click on DTS and open package and select each package and save them to sql server...

Since Im dealing with live environment without any realtime/live help...I just wanted to confirm the way im doing it and would like to take suggestions and precautions to ensure proper functioning of the same at the end.

Also after opening dts packages by choosing design package when i say exit edit it says sql server cannot be found kinda error so I closed it directly...I guess it will not have any harm but again wanted to make sure of the same.
If at all possible screenshots wud be gr8 help...pointing to good sources is also fine(not lengthy pls, time constraint).Every little help is much appreciated at its best
Thanks in Advance.
0
Comment
Question by:anilkullam
  • 4
  • 3
8 Comments
 
LVL 39

Accepted Solution

by:
lcohan earned 500 total points
ID: 35109877
I believe you get the error because of rights or connectivity issues and here's what I would do with one package at a time:

Right click on it on the source server and save it as a File instead, then copy the file to the new server and try to import it or simply drag/drop in the MSDB packages window on the new server where they will open in designer. Here most likely you must update at least package connection mamnager credentials as at least the server names would change.
0
 

Author Comment

by:anilkullam
ID: 35110030
@ Icohan, thanks for the reply...when right clicked on the package i dont see an option to save it as a file to go ur way...and can u pls elaborate abt the further steps..MSDB packages window and connection manager credentials pls

And can you see options in fixing my way, cuz i cannot copy directly n has to ask for windows team help to move btn servers...as my folder with dts packages is already there...how do i get it work
0
 
LVL 39

Expert Comment

by:lcohan
ID: 35110223
Sorry for not including too much detail as I don't have a SQL 2000 environment anymore...Do you need to copy them from SQL 2000 server to SQL 2005?
0
 
LVL 39

Expert Comment

by:lcohan
ID: 35110371
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 39

Assisted Solution

by:lcohan
lcohan earned 500 total points
ID: 35110448
Actualy you should be able do the following from an old 2000 to 2005:

http://www.sqldts.com/204.aspx

This will transfer all local packages from the source to the destination server. You may however only wish to transfer selected packages. This can easily be achieved by using a SQL Query for the source instead of using sysdtspackages directly. Some sample queries are described below.

You can specify the packages to transfer by name:

SELECT * FROM dbo.sysdtspackages
WHERE [name] IN ('MyPackage_Number_1', 'MyPackage_Number_2')


Every time you save a local package a new version is stored. Every version is an individual row in sysdtspackages. You may only want to transfer the latest or current version, such as when moving between a development and production environment.

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]


You may wish to transfer the most recent n versions:

SELECT T1.* FROM dbo.sysdtspackages AS T1
INNER JOIN (SELECT T2.[name] , T2.[id], T2.[createdate]
  FROM dbo.sysdtspackages T2
  GROUP BY T2.[name], T2.[id], T2.[createdate]
  HAVING T2.[createdate] IN (SELECT TOP n T3.[createdate]
    FROM dbo.sysdtspackages T3
    WHERE T2.[id] = T3.[id]
    ORDER BY T3.[createdate] DESC) ) AS T2
ON T1.[id] = T2.[id] AND T1.[createdate] = T2.[createdate]


This works quite happily between all current versions of SQL Server, despite the addition of the packagetype column in SQL Server 2000. If you don't supply a value it will be populated with the default of 0.

sysdtspackages is an undocumented system table, and as such may change in future versions of SQL Server. Any changes may invalidate this process, but in the meantime it works just fine, and is definitely the most efficient method of transferring packages.

0
 

Author Comment

by:anilkullam
ID: 35114074
Hi am sorry I have already went thru the articles but as said in the prob both are sql server 2000 versions only n not from 2000 to sql server 2005.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35118741
>>Hi am sorry I have already went thru the articles but as said in the prob both are sql server 2000 versions only n not from 2000 to sql server 2005. <<

Than you obviusly did not read the article posted by lcohan here http:#a35110448.  This article has nothing to do with SQL Server 2005 and is only intended for SQL Server 7/2000.
0
 

Author Closing Comment

by:anilkullam
ID: 35180282
It worked the simple way...just open the DTS package and then save them onto the target server just by typing the target server instead of the default source server in the dropdown/editable textbox.

Anyways thanks for all the help guys.
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now