Solved

Copying DTS packages btn sql 2000 servers

Posted on 2011-03-11
8
237 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 39

Expert Comment

by:lcohan
ID: 35110371
0
 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Some sers suddenly getting error popup msg 28 89
T-SQL: Episode III - Revenge of The Dude 24 53
Get the latest status 8 32
Show Results for Latest DateTime in a View 27 25
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
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.

809 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