I know you can use sp_enum_dtspackages to list the DTS packages
on a given server, but is there a way to list the individual step names, etc.?

TIA
Cheers,
dj

Hmm... I guess I could always use sp_help_jobsteps. Doh!

"dj" wrote:

In message <51F60320-BFF4-4951-957D-1A451B2D18F4 (AT) microsoft (DOT) com>, dj
<dj (AT) discussions (DOT) microsoft.com> writes
No. When packages are save to msdb..sysdtspackages, some information is
persisted in the relational table, name version and similar, but the
rest such as steps is part of the package which is in COM structured
storage in the image column of that table.

Do get this info you would need load the package into the object model
and enumerte the steps. Pseudo code-

Declare PkgObject as DTS.Package

PkgObject.LoadFromSQLServer( info from sysdtspackages or
sp_enum_dtspackages )

For Each Step in PkgObject.Steps
-- Do stuff here
Next

You could do this in T-SQL, but only through the OLE stored procedures,
create the object, call load, enumerate step objects. A start point
would the spExecutePKG.sql and spDisplayPKGErrors.sql code from the link
below as the do the load, and step enumeration, albeit aimed at
executing a package and check each step for an error afterwards.


Archive
(http://www.sqldts.com/default.aspx?273)

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

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