Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
DaleHughes
Starting Member
7 Posts |
 Posted - 2002-06-20 : 15:20:52
|
| Friends, is there a more efficient way to migrate a list of DTS packages between servers rather than going into each individual package and doing a "save as" to the other server? |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-06-20 : 15:36:38
|
| I wish, Dale, oh gawd how I wish.....By the way, don't forget to change all your server specific references in the package like connects, log to server, and ado code in ActiveX tasks and the like . . .<O> |
 |
|
|
DaleHughes
Starting Member
7 Posts |
Posted - 2002-06-21 : 09:43:24
|
| I found it! If you save your dts packages under local packages, they are stored in the msdb.dbo.sysdtspackages table.1) create a new dts package with connections to the two servers2) create a datapump between the connection3) because you cannot select the sysdtspackages table from the pull-down list, you need to use disconnected edit to manually modify the datapump to set the SourceTable and the DestinationTable to [msdb].[dbo].[sysdtspackages]4) now close the disconnected edit5) choose properties of the datapump and let dts perform the auto-mapping in the transfermations tab.6) run it!This will transform the rows in sysdtspackages from one server to the other, and PRESTO! All the packages have been moved, including all the version history!You could also set up a source query to select specific packages based on the name, or only the current version based on the create date.Happy Happy Joy Joy :) |
|
|
|
DaleHughes
Starting Member
7 Posts |
Posted - 2002-06-21 : 09:46:32
|
| Also, I found this info in the FAQs on www.sqldts.com |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-06-21 : 10:23:45
|
quote:
I found it! If you save your dts packages under local packages, they are stored in the msdb.dbo.sysdtspackages table.1) create a new dts package with connections to the two servers2) create a datapump between the connection3) because you cannot select the sysdtspackages table from the pull-down list, you need to use disconnected edit to manually modify the datapump to set the SourceTable and the DestinationTable to [msdb].[dbo].[sysdtspackages]4) now close the disconnected edit5) choose properties of the datapump and let dts perform the auto-mapping in the transfermations tab.6) run it!This will transform the rows in sysdtspackages from one server to the other, and PRESTO! All the packages have been moved, including all the version history!You could also set up a source query to select specific packages based on the name, or only the current version based on the create date.Happy Happy Joy Joy :)
except ...quote:
By the way, don't forget to change all your server specific references in the package like connects, log to server, and ado code in ActiveX tasks and the like . . .
There is no way to move these references automagically. Though moving DTS packages via DTS is a good way to minimize the effort required.setBasedIsTheTruepath<O> |
|
|
|
|
|
|
|
|
migrating DTS packages between servers