Moving DTS packages from box to box

Question

Moving DTS packages from box to box

Terri Rogers

SSC Enthusiast

Points: 128
More actions
August 24, 2004 at 12:51 pm

#86502

This is a similar question to one I see posted, but which is still awaiting a reply. I need to move a DTS package from one server to another. I suspect I can create a package to move this package? Or is there a package available that will do this? Both boxes are sqlsvr 2000. Any specifics anyone can provide would be greatly appreciated. Thanks!
Terri

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

DCPeterson SSCoach Points: 15266 More actions · Answer 1

The simplest method of moving DTS packages from one server to another is to open the package in designer and under the "Package" menu select "Save As" you will be able to select the new server and a new name if you want.

If you have a bunch of packages you can actually just export the contents of the msdb.dbo.sysdtspackages table to the new server. This method works, but is not supported officially by MS so proceed with caution.

/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

*****************/

Terri Rogers SSC Enthusiast Points: 128 More actions · Answer 2

Thanks! I just discovered the ol' structured storage file option as well and moved it that way.... my devs & I appreciate the quick response!

mike gercevich SSCrazy Points: 2961 More actions · Answer 3

Do a Google search on "Bruce Szabo" and "DTS Store". He is a good friend of mine that wrote a great article on this very subject.

DSP-120308 Hall of Fame Points: 3959 More actions · Answer 4

If you visit this site http://www.sqldts.com/ they have a very handy tool called DTS Backup that has been working well for me. I use it to backup all DTS packages to one centralized location, just in case!!

JStiney Say Hey Kid Points: 671 More actions · Answer 5

DTSbackup 2000 will do what you want and a lot more. We have been using it to move lots of packages around to different machines. There is nothing that I have seen that comes close.

Terri Rogers SSC Enthusiast Points: 128 More actions · Answer 6

Terri Rogers

SSC Enthusiast

Points: 128

August 25, 2004 at 8:42 am

#520529

Wow, thanks for great advice, everyone!

bellis SSCarpal Tunnel Points: 4667 More actions · Answer 7

Check out the following article on http://www.SQLDTS.com

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

From the article...

To transfer local packages directly between servers follow the simple steps below:

1 Create a new package, and add two connections, one for the source server and one for the destination server.

2 Select the two connections and add a DataPump (Transform Data) task.

3 SQL Server 7.0

Open the DataPump task properties sheet. System tables are unavailable in the "table/view" drop-down box, so you will have to type it in manually, [msdb].[dbo].. Do the same for the destination, and let the transformations auto-generate.

3 SQL Server 2000

Do not open the DataPump task properties yet. First open Disconnected Edit from the package menu. Navigate to your DataPump task and set the SourceObjectName and DestinationObjectName properties to [msdb].[dbo].[sysdtspac kages]. Now open the DataPump task properties and go to the Transformations tab to allow the transformations to auto-generate. In SQL Server 2000 you no longer have the ability to manually enter a table name for the source or destination object as in SQL Server 7.0, but this method allows you to overcome this as described.

4 You can now execute you package.

This will transfer all local packages from the source to the destination server.

Piotr Kustra Newbie Points: 9 More actions · Answer 8

Hi

I plan to have the production SQL DB and the development SQL DB. The idea is to make any new development on the development machine and once tested and approved move it to the production SQL DB. Although the structure and all DTS will be identical (that is before changing something on the development SQL) the names of these two machines will be different. I think of using the simplest method for moving a modified DTS from development to production and that is by saving all DTS into a flat file (one of the SQL options). My question is if the name of the SQL Server machine is stored anywhere in the DTS, which may cause some problems when creating a new DTS on one machine and then trying to use it on a different one.

Does anyone have a solid experience in this area?

thanks in advance

DSP-120308 Hall of Fame Points: 3959 More actions · Answer 9

I always try to set the connections for the packages to use (local) as the server instead of a server name (along with a trusted login). I can move packages between servers with having to modify anything this way.

barsuk SSChampion Points: 12280 More actions · Answer 10

you could also try to copy either a single row, If you know the name of your dts package, or the whole table in msdb: sysdtspackages.

After doing that you might want to clean up connections, users, links, path(since on different servers paths are usually different) etc.

Backup your msdb on both servers before doing that and Excercise caution.