Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Copying DTS packages btn sql 2000 servers

Posted on 2011-03-11
8
Medium Priority
?
243 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 40

Accepted Solution

by:
lcohan earned 1500 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 40

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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 40

Expert Comment

by:lcohan
ID: 35110371
0
 
LVL 40

Assisted Solution

by:lcohan
lcohan earned 1500 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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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…

886 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