sql server dts: To automate DTS-searching process associated with a certain stored procedure or tabl
in case of failure to get data for tables or other errors
I wanted to make ASP to display all the information of DB, tables
stored procedures associated with each table, and DTS steps excuting each stored procedure

Currently, I found system tables enabling to do so
but tables containing information of DTS steps excuting certain stored procedures
I understand that they have tables for DTS packages or steps.. but no tables linking them with stored procedures.. =

You are right there is no dependency mapping information that says "This DTS
package executes this SP".

You could loop through the tasks within a package looking for them in things
like

SourceObjectName
SQLStatement

This would then need to be compared against your list of Stored Procs to
find out which of thise objects were tables and which were SPs.



--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


[quoted text, click to view]

Thank you for the help.

I have another question for the way I can loop thru the steps.. As I understand, there is no table which contains DTS steps except for logs left. Then what should I use

You are right, as I previously stated there is no internally held reference
table that says "This package executes this stored procedure".

Looping through the Tasks not Steps as the Tasks contain the info you
require. Think of Steps as the containers and physical representation of
what is going to happen and the Tasks as the workers.


To loop through packages held in MSDB you can use

Enumerating DTS Packages using VB.Net
(http://www.sqldts.com/default.aspx?250)

Once you've grabbed the package you can look inside the Tasks. I presume
that your three main areas of investigation are going to concern

ExecuteSQL tasks - SQLStatement
DataPumps - SourceSQLStatement
Data Driven Queries - - SourceSQLStatement

Once you grab these properties from the Tasks then you can parse them to
compare against your Stored Procs.


--
--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

[quoted text, click to view]
understand, there is no table which contains DTS steps except for logs left.
Then what should I use?
[quoted text, click to view]
I also need mapping information between steps and packages..