Unanswered: what tables are used/altered in DTS pacakge
We have a legacy SQL Server 2000 database with over 150 DTS packages, all written by different people who have all left the company. Nobody knows what tables are used, updated, deleted, inserted into in the different DTS's.
My new assigned job is to document all those DTS's : on one hand extract the logic behind the packages and on the other hand identify the tables and columns they use, how they are used: be it read, inserted into, updated, deleted from .... Knowing that people are still modifying those DTS's, I figured out the moment I would fully have documented a DTS it may be altered less than an hour later ...
Is there an automatic way to extract a CRUD from all those DTS's? That I could run nightly to extract that information?
I was thinking of exporting the DTS to VB-scripts then parse them somehow. The problem to make a CRUD is to parse the SQL statements to find out which columns of which tables are read, which ones are updated, ...
Any ideas would be very much appreciated.
The next part of my assignment will be much more fun: fully re-engineer the database on SQL Server 2005 (or 2008)
With kind regards . . . . . SQL Server 2000/2005/2012
Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2. Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages