I'm trying to batch-update a slew of legacy DTS packages that have gobs of explicit references to the old servername. At the same time, I want to get them off of the server and into sourcesafe.

No problem, being an old VB guy, I wrote some code using the DTS COM API to pull everything off the server, search & replace the old servername, and saves them off as structured storage files. The problem is the screen formatting is lost. There's lots of packages with multiple datapump tasks between the same two connections. After saving via the COM interface and reopening in Ent Mgr, there's two connections for every datapump task, and precedences all over the place. Even though the package is functionally the same (the added connections all reference the same original two connections), the folks who use these things are going to freak.

The code is below. Any tips? Should I be using the Application object to retrieve the package info? I can't find much documentation on the DTS app object.


Public adoConnect As New ADODB.Connection
Public mobjDTSpkg As DTS.Package2
Public mstrSQL As String
Public Sub SavePackages(mstrServerName as String)
adoConnect.Open "Provider='SQLOLEDB';Data Source='" & mstrServerName & "';Initial Catalog='msdb';Integrated Security='SSPI';"
mstrSQL = "select DISTINCT name FROM [sysdtspackages]"
adrtemp.Open mstrSQL, adoConnect
Do Until adrtemp.EOF
Set mobjDTSpkg = New DTS.Package2
mobjDTSpkg.LoadFromSQLServer mstrServerName, , , DTSSQLStgFlag_UseTrustedConnection, , , , adrtemp("name").Value
mobjDTSpkg.SaveToStorageFile mstrStructuredStorageFilesLocation & "\" & mobjDTSpkg.Name & ".dts"