Enumerate DTS Steps? - 03-09-2005 , 09:19 AM
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.?
RE: Enumerate DTS Steps? - 03-09-2005 , 09:25 AM
Hmm... I guess I could always use sp_help_jobsteps. Doh!
Re: Enumerate DTS Steps? - 03-09-2005 , 01:28 PM
In message <51F60320-BFF4-4951-957D-1A451B2D18F4 (AT) microsoft (DOT) com>, dj
<dj (AT) discussions (DOT) microsoft.com> writes
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
For Each Step in PkgObject.Steps
-- Do stuff here
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.
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com
PASS - the definitive, global community for SQL Server professionals