Using SQL DMO to read DTS Packages

There is a column name and defination change and lots of code has to be examined. The developers were worried about looking through the many DTS packages. Happily the contents can be queried using sql-dmo.

The short of it is looping through all the DTS packages, one at a time setting a reference to is with the DTS.Package2 object, and then looping through the tasks and the task’s properties.

I made a table to insert each package’s, task’s, and property’s values, including the server too and then was able to query for the column in questio. Woo-hoo!

This came in handy for looking for code within view, procedures, etc:
exec sp_msforeachdb ‘use [?] ; select distinct db_name(), name from sysobjects so join syscomments sc on = where sc.text like ”%<>%”’

This entry was posted in SQL DMO and tagged . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *