• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 389
  • Last Modified:

SQL 2000 DTS: How to find package(s) that update a particular table

I am working with a massive DTS project.  It's poorly written & we're replacing it ... but I have to maintain it a little longer ... and the last developer who really knew the thing is long gone.

So ... I want to know which package (of about 100) updates a particular table.

To find a view or procedure, I would query syscomments.  Is there a similarly good way to find a DTS package that updates that table?

Daniel Wilson
Daniel Wilson
4 Solutions
James MurrellProduct SpecialistCommented:
If the dts package stored in MSDB, it will be stored in sysdtspackages table but you can't query the Package configuration...because it is stored in hex format in sysdtspackages.packagedata column.

You may need to write VB code...to do this...OR save the package as VB file and search...
Check the following thread for script or you can buy the product if your company is ready to pay...http://www.dtspowersearch.com

Anthony PerkinsCommented:
The only way you could do that is if the queries were dynamic.  In other words, they were not using Stored Procedures.  If the queries are dynamic you can then search for the name of the table. In order to make it more accurate, it would help if you could narrow the queries down to just Execute SQL Task.  If they are buried also in ActiveX Script tasks than you may get a few false postives.

Let me know if this is something you would like to pursue.
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

You may be able to use this:
To dump the DTS properties to a file and then check the file for references to a table.
Daniel WilsonAuthor Commented:
Thanks, guys!  Several good ideas ... dtspowersearch appearing at this point the most promising.
Anthony PerkinsCommented:
>>dtspowersearch appearing at this point the most promising.<<
For a free solution, all you need is about 20-30 lines of VBScript code.

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now