Search DTS Package

  • Hi All,

    A DTS package runs on a schedule that inserts data into a table on another server. I know the name of the table but I do not know the dts that inserts/updates that data. So how do I search for a dts package that updates a certain table in sql server 2005?

  • Did you try checking the logs?

    ¤ §unshine ¤

  • Thank you Sunshine. I could not find it in the logs.

  • I do not think you can get it directly. DTS packages are saved in binary code.

    You may set a profiler, or trigger to see what processes are running when you table is modified.

  • If these are legacy DTS package, not SSIS packages, you can save them as VB files and search them with a text editor. There are scripts in the Script section of this site that will save all packages in an instance to files. Try searching for something like, "save as file".

    Greg

  • Thank you Greg. Yes the packages were created using SQL 2000. I was not able to find any script on this site to save the files, but I tried saving it to vbfile manually and that is a tremendous help. Thanks again.

  • You can use .NET to read each of the DTS packages and create a summary of the package including connections and code using the Microsoft DTS Com objects. We create a directory for each sql server and put the DTS summaries there using txt extension.

  • BP-503183 (5/14/2010)


    You can use .NET to read each of the DTS packages and create a summary of the package including connections and code using the Microsoft DTS Com objects. We create a directory for each sql server and put the DTS summaries there using txt extension.

    Could you please post the .NET code you are making use of for creating such a nice listing of dts package.

  • I found some of this code online a couple years ago, I don't have the original authors name. I have modified over the years to work for our setup. The attachment is vb code in a text file.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply