March 19, 2008 at 2:40 pm
I am trying to do some cleanup.
This SQL get me all the number of DTS packages I do have:
select distinct
name as DTS_Name,
owner as DTS_Owner,
max(createdate) as DTS_Created,
id as DTS_ID
from msdb..sysdtspackages
group by name, owner, id
order by name
and this one gives me the number of Jobs I do have
SELECT *
FROM msdb..sysjobs
order by date_created desc
Now, here is the question, how do I relate the Jobs with the DTS packages, after that I will be able to do some cleanup and eliminate all the unused DTS packages.
Any advice......
Thanks!
March 19, 2008 at 3:35 pm
It depends on how packages are run in the jobs. If the DTSRUN command is unencrypted, you can query msdb.dbo.sysjobsteps to find the name of the package run. If it's encrypted, which is the default if you right-click on a local package and select "schedule package", it's a little more involved.
See this thread for instructions on how to read an encrypted DTSRUN command: http://www.sqlservercentral.com/Forums/Topic454767-9-1.aspx
Greg
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply