March 9, 2007 at 6:25 am
Is there an easy way to see if and which DTS packages are called by jobs? I am checking DTS packages on SQL2000 in preparation to a migration to SQL2005 and from jobnames and jobsteps it is unclear what packages are called.
Greetz,
Hans Brouwer
March 9, 2007 at 11:15 am
If the jobs were created by Enterprise Manager, the description field in sysjobs should show the DTS package name in the following format: "Execute package: <PackageName>"
select * from msdb.dbo.sysjobs
OR, you could query that and sysjobsteps like this
select jobs.name, jobs.description, steps.step_name, steps.command from msdb.dbo.sysjobs jobs
inner join msdb.dbo.sysjobsteps steps
on steps.job_id = jobs.job_id
where left(steps.command,6) = 'DTSRun'
Which would show definitively which jobs are running DTS Packages, but unfortunately not necessarily which particular package.
Sincerely,
- Dan B
March 9, 2007 at 12:22 pm
Amazing you would ask this....I had the same question and found an awesome answer on this site. I no longer have the path to the originator...but...
1. Copy the DTSRUN line (everything including the DTSRUN)
2. Open a Windows Command Line window
3. Paste the DTSRUN line into the CMD window.
4. To the end of the line, add /!X /!C
/!X = do not execute /!C = copy results onto Windows Clipboard
5. Run the command
6. Open Notepad
7. Click Edit>Paste
That will paste the actual command into Notepad and it will show the name of the package.
Update: 9 March 2007
Original SQLServerCentral.com link that led me to the answer:
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=92&messageid=194277
In that discussion, one member references a thread on SQL Magazine, where member jamespua provided the solution I gave above:
http://sqlforums.windowsitpro.com/web/forum/messageview.aspx?catid=80&threadid=45680&enterthread=y
-SQLBill
March 9, 2007 at 12:32 pm
Wow... Good answer!
-Dan B
March 10, 2007 at 4:33 am
Tnx! This is really helpful!
Greetz,
Hans Brouwer
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply