April 28, 2004 at 7:44 am
I am trying to transfer one DTS package from one SQL server to another on different boxes. I can do "save as" which will move the package however I want to transfer all versions with the DTS package not just the open version. Does anyone know an easy way to do this?
April 28, 2004 at 8:01 am
Use save as but change the location to structured storage file(file.dts). Transfer this file to you server and open there. It should work. Please let me know.
Regards
JFB
April 28, 2004 at 8:01 am
Check out the utility for backup and restores of DTS pacakges out on http://www.SQLDTS.com
April 28, 2004 at 9:22 am
All,
I tried the file.dts and this did not transfer versions. The backup DTS 2000 utility does not transfer versions as well. Here is what I did to get this to work (I did get this from http://www.SQLDTS.com):
Created a DTS to transfer data from one server to another using the transform data task. There was still a problem with selecting the sysdtspackages table from the msdb (this table holds the DTS image/BLOB data for DTS packages). This is a system table and DTS will not allow you to transfer or overwrite unless you tell it to. You can change this in the "disconnected edit" option in the package menu. Select the data pump task and edit your source and destination table names. Then when you go back into your transform data task the table will be in the list of selectable tables. then you just transfer it.
Thanks all for the response. I was pointed in the right direction and know a great new site for DTS info. Thanks again!
Nszczepnaski
September 23, 2004 at 1:06 pm
I hope you are still checking this posting. I figured out how to transfer a package and all its versions.
1. Enter the pacakage and select save as
2. When the save as dialog comes up change the name of the underlying server to the name of the server you where you want the dts package.
3. Change nothing else.
4. Once the package saves it will automatically change you to the new server. The destination server was already registered with my enterprise manager. I do not know if that is a requirement.
I just did this and it transferred the package and its previous versions. I know this because the dts package did not exist on the destination server prior to the transfer.
I hope this helps. Email me if you have any additional problems.
Justus
Justus maybe blind, but Justus is always swift and fair.
September 23, 2004 at 2:04 pm
Thanks for the reply but I wrote a DTS package that will transfer all your DTS packages at once which was my goal.
I set up connections between the servers and basically copied all the data from the sysdtspackages from one server to the other. You have to enable SQL server to let you write to this system table as well.
-NGS
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply