Our production DTS environment includes hundreds-and-hundreds of packages across dozens of servers. We need to be able to do where-used/impact analysis before making a fairly significant business change (a division is splitting in two)

Outside opening EVERY package, is there a way to do things like searching for hard-coded strings to facilitate determining the scope of our change effort?

In message <474AD0BD-76F6-4C19-AAF6-544AF4BDFAC1 (AT) microsoft (DOT) com>, Kevin L
<kevin.luke (AT) ecolab (DOT) com> writes
You could automate this process. You can enumerate packages and load
them using the object model You can then enumerate tasks and
collections, and look at their properties.
You could then write the details of each package found, each task found,
each connection found and any intersting properties to a location of
your choice.

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

Thank you for the idea. I've done a bit of DMO, but it's been awhile. Do you, or does anyone have some sample code to do this kind of thing

thanks
Kevin

You will only be using DMO to enumerate the servers.

The way I do it is this

I have a ServerGroup called Production
I have all my production servers within.

I enumerate through the RegsisteredServers collection pulling each one out.

I then use the DTS object model to open each package.

For Each orServer In SQLDMO.ServerGroups("Production").RegisteredServer s


Set oServer = New SQLServer

oServer.Name = orServer.Name
oServer.LoginSecure = True

oServer.Connect


'Now go get the packages


oServer.DisConnect

Next

End Sub

--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Kevin L" <kevin.luke (AT) ecolab (DOT) com> wrote

In message <AA86C4F9-EAC1-427D-8746-0A9EEF64C7B6 (AT) microsoft (DOT) com>, Kevin L
<kevin.luke (AT) ecolab (DOT) com> writes
You don't want need DMO in this case, although you could use it to
enumerate the SQL Servers to connect to, but for the packages proper,
you want the DTS Package Object Library.

For enumerating packages, try the Transfer Packages Sample Application
which demonstrates this-
Tools & Tasks
(http://www.sqldts.com/default.aspx?272)


For the enumeration of the connections and such like it is just like any
other collection. Try the ScriptPkg utility that came with SQL 7.0, as
could serve as a start point for your code.

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org