Copying DTS packages btn sql 2000 servers

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.
anilkullamAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
lcohanConnect With a Mentor Database AnalystCommented:
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
 
anilkullamAuthor Commented:
@ 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
 
lcohanDatabase AnalystCommented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
lcohanConnect With a Mentor Database AnalystCommented:
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
 
anilkullamAuthor Commented:
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
 
Anthony PerkinsCommented:
>>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
 
anilkullamAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.