Solved

Copying DTS packages btn sql 2000 servers

Posted on 2011-03-11
8
233 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
Comment Utility
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
Comment Utility
@ 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
Comment Utility
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
Comment Utility
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 39

Assisted Solution

by:lcohan
lcohan earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
>>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
Comment Utility
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

762 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

9 Experts available now in Live!

Get 1:1 Help Now