Hello:

I am working with SQL Server 2000.

I need to search thousands of DTS packages across multiple servers to locate
those DTS Packages that reference a specific server. The server is being
replaced and the server's name is being changed to accommodate corporate
guidelines.

Is there a way to search DTS Packages without manually checking each one. I
know that DTSRun can output an existing DTS Package to a file (without
executing the DTS Package) but the output is a structured storage file which
is encrypted. If I could find a way to output them as .bas (VB files) then I
could output all DTS Packages and search the output directory for the
server's name.

Any help would be appreciated.

Buddah1957

You could grab ScriptPKG from the SQL Server 7 CD and use that but all
this will do is traverse the object model as well as you can do it and
in your way you would only do the connections where ProviderID =
"SQLOLEDB"

There is no other way I know to do this.

This is one of the advantages of having packages read for info from an
External source. That way you just change the source for the reference.

Allan

"Buddah1957" <Buddah1957 (AT) discussions (DOT) microsoft.com> wrote

try this if you are familiar with VB modify the below code and search your
server name in the package connection property; hope this helps you.
;
Dim oDTSApp As DTS.Application
Dim oPkgSQLServer As DTS.PackageSQLServer
Dim oPkgInfos As DTS.PackageInfos
Dim oPkgInfo As DTS.PackageInfo
Dim oPkg As DTS.Package2
Dim oConn As DTS.Connection2
Dim oTask As DTS.Task
Dim oTaskProp As DTS.Property
Dim strDestinationObject As String
Dim strSourceObject As String
Dim strSQL As String

strSource = "SOURCE"
strServerNameToCheck = "CHECKSERVER"
strDestination = "DEST"

' Enumerate Packages
Set oDTSApp = New DTS.Application
Set oPkgSQLServer = oDTSApp.GetPackageSQLServer(strSource, "test",
"test", DTSSQLStgFlag_Default)
Set oPkgInfos = oPkgSQLServer.EnumPackageInfos("", True, "")

' Loop through Packages
Set oPkgInfo = oPkgInfos.Next
' "Column does not exist" error on SQL 7.0 because sysdtspackages lacks
packagetype column

Do While Not oPkgInfos.EOF
Set oPkg = New DTS.Package2

' Load the Pkg
oPkg.LoadFromSQLServer strSource, "test", "test", , "", "", "",
oPkgInfo.Name, True

' Change SQL Connections
For Each oConn In oPkg.Connections
If oConn.ProviderID = "SQLOLEDB" And UCase(oConn.DataSource) =
strServerNameToCheck Then

'Save the required package any where else or to file

oPkg.SaveToSQLServer strDestination, "test", "test",
DTSSQLStgFlag_Default, , , , True

End If
Next


oPkg.UnInitialize

Set oPkgInfo = oPkgInfos.Next
Loop


' Clean Up
Set oConn = Nothing
Set oPkg = Nothing
Set oPkgInfo = Nothing
Set oPkgInfos = Nothing
Set oPkgSQLServer = Nothing
Set oDTSApp = Nothing




"Buddah1957" wrote: