How to find which jobs run which packages
-
Hi All,
We've got a server that has a large number of DTS packages on it, some of which are called by other packages and the rest are kicked off by scheduled jobs. Naming standards are not the best
Does anyone know of a script that I can run to establish which DTS packages are being started by scheduled jobs??? Much appreciated
The Aethyr Dragon
Cape Town
RSA -
Dear The Aethyr Dragon
The information you are looking for is in msdb. The table sysdtspackages have the identity of the package and the DTS name. The scheduled jobs tell you which id package is running.
I hope this helps.
-
You could query the sysjobsteps table in msdb looking for a DTSRUN command. Something like this:
select * from sysjobsteps
where command like 'DTSRUN%'
This will only help you if the DTSRUN command hasn't been encrypted.
Greg
Greg
-
The DTSRUN is not encrypted, it's the rest of it that is - that's the bit I'm trying to unravel. The original authors just right-clicked and scheduled - so it's got the whole encryption going. I can find a bunch of scheduled jobs that kick off DTS packages the trick is matching them up. This is a client's production server - so not much scope to play around with anything.
Once I've managed to find a way to match apples and oranges, the next step is to find out which packages call which other packages.... (automated by preference but at least in this case manual is an option!)
TIA
The Aethyr Dragon
Cape Town
RSA -
I just remembered another thread I participated in and it had a couple of possible solutions for the encrypted package name problem. I haven't tried either method so I can't vouch for either. Here's the thread:
http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=110&messageid=232301
Greg
Greg
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply