I'm trying to update all connections in all dts packages
on the server if the specified value is found in the
connection data source. The code seems to work fine as
in there are no unexpected errors or halts except that the
change doesn't actually occur. After the script runs
seemingly successfully, I open a package to confirm the
change but the connection still has the old data source!
Any thoughts????

'Connect to server and get a list of all packages
Set oDTSApp = New DTS.Application
Set oGetPkg = oDTSApp.GetPackageSQLServer(svr, uid, pwd,
auth)
Set cPkgs = oGetPkg.EnumPackageInfos("",
True, "") 'EnumPackageInfos
(PackageName,ReturnLatest,PackageID )
Set oGetPkg = Nothing

'Load each package and carry out the required changes
For Each oPkg In cPkgs
On Error GoTo ErrorHandler
pkgname = oPkg.Name
myPkg.LoadFromSQLServer svr, uid, pwd, auth, , , ,
pkgname

'Update connection datasource
Set cConns = myPkg.Connections
For Each oConn In cConns
If oConn.ConnectionProperties("Data Source")
= "WNCHASQ02" Then
oConn.ConnectionProperties("Data Source")
= "(local)"
Debug.Print myPkg.Name & ": " & oConn.Name
End If
Next
'Need to reset the package to be empty before the next
one begins
myPkg.UnInitialize
Set myPkg = Nothing
Next oPkg

Looking at your code I cannot see anything wrong with it except (Goto ErrorHandler not defined in code, and vb property), does this actually work in DTS(ActiveXscript

possibility is scripting out dts packages use dtsdemo.exe into a text file *.bas then using REPLACE function in vbscript to search and replace with "(Local)" then use Vbscript to uncomment the line
[goPackage.SaveToSQLServer "(local)", "sa", ""]
and comment the line [goPackage.Execute] to save it iinstead of executing

rather long winded thoug

You are not saving your changes, so they will not persist beyond the
currently loaded instance of the package.

Try the Transfer Packages Sample Application here for an example you can
adapt-
Tools & Tasks
(http://www.sqldts.com/default.aspx?272)


--
Darren Green
http://www.sqldts.com


"kristen" <khodgesN (AT) SPAM (DOT) csr.com.au> wrote

And there it is!

Thanks Darren!

It's always the simple things!