Search DTS Package

Question

Post reply

Search DTS Package

ramadesai108

SSChampion

Points: 10609
More actions
March 13, 2008 at 7:56 am

#121065

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?

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

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

sunshine-587009 SSCrazy Eights Points: 8981 More actions · Answer 1

sunshine-587009

SSCrazy Eights

Points: 8981

March 13, 2008 at 8:17 am

#789085

Did you try checking the logs?

¤ §unshine ¤

ramadesai108 SSChampion Points: 10609 More actions · Answer 2

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

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 3

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.

Greg Charles SSC-Forever Points: 45403 More actions · Answer 4

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

ramadesai108 SSChampion Points: 10609 More actions · Answer 5

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.

BP-503183 SSC Eights! Points: 842 More actions · Answer 6

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.

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 7

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.

BP-503183 SSC Eights! Points: 842 More actions · Answer 8

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.