SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Editting DTS packages


Editting DTS packages

Author
Message
John R. Yori
John R. Yori
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 1

I am in the process of planning for moving a database to a new sql2000 instance. The problem is I found out there is several hundred DTS packages saved as structured storage files (.DTS) that will need to be editted to reflect the new instance name. The only way I know how to do this is to edit each one individually. Does anyone know of any tools or tricks to do this quickly?



WCDBA
Martin D. Cymerman
Martin D. Cymerman
SSC-Enthusiastic
SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)

Group: General Forum Members
Points: 146 Visits: 17
The only thing I would suggest is to save the DTS packages to VBscript and do your editing that way.

HTH
Marty
Greg Charles
Greg Charles
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5455 Visits: 5925

I don't know of a way to change something in all the packages without opening each one, but here's a thread that describes several ways to avoid that situation: http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=19&messageid=102397

Greg



Greg
Glenn Buckingham-230924
Glenn Buckingham-230924
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 2

You can load each File up with VB, Find the Object you need to change in the object model, and then save them Back. I manipulate DTS packages all the time this way...


Perry-300990
Perry-300990
SSC-Enthusiastic
SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)

Group: General Forum Members
Points: 119 Visits: 6
You'll probably have to loop through the files, and for each one, call a function to fix it.

In the function, you probably have to create an object of type DTS.Package, and call its LoadPackage routine, giving it the filename. Then you have the DTS.Package object read to be edited, and then, I think the package object has a connections property, so probably you loop through all the connections, adjusting some property on each.

As you can guess from how fuzzy I am on the details here, I've not done this.

I think you could do the whole thing in VBA or VBS or VB, probably.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search