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

Posted on 2009-02-19
Last Modified: 2013-11-30
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?

Question by:Daniel Wilson
    LVL 31

    Assisted Solution

    by:James Murrell
    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 do this...OR save the package as VB file and search...
    LVL 15

    Accepted Solution

    Check the following thread for script or you can buy the product if your company is ready to pay...

    LVL 75

    Assisted Solution

    by:Anthony Perkins
    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.
    LVL 30

    Assisted Solution

    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.
    LVL 32

    Author Closing Comment

    by:Daniel Wilson
    Thanks, guys!  Several good ideas ... dtspowersearch appearing at this point the most promising.
    LVL 75

    Expert Comment

    by:Anthony Perkins
    >>dtspowersearch appearing at this point the most promising.<<
    For a free solution, all you need is about 20-30 lines of VBScript code.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    I recently came across an interesting Question In EE ( and was puzzled about how to achieve that using SSIS out of the box tasks, which was i…
    This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now