getting a list of jobs and their associated DTS

• mark lake

  Say Hey Kid

  Points: 702

  More actions

  July 10, 2006 at 9:10 am

  #67603

  Hi,

  I've been asked to compile a list of all our jobs, their status(enabled/disabled) the associated step names/id's and the associated commands/DTS.

  Is there a quick way to do this?  Or is it a case of going through each job and looking at the steps and tracing it back to the DTS?

  I fear there is no quick way but I'd really appreciate it if someone could inform me otherwise!!

• Erik Kutzler

  SSCarpal Tunnel

  Points: 4287

  More actions

  July 10, 2006 at 10:28 am

  #648288

  You could always query the sysjobs & sysjobsteps tables and paste the results in a spreadsheet.

   

  SELECT

   sj.[name],

   sj.[description],

   sj.enabled,

   ss.step_id,

   ss.step_name,

   ss.command

  FROM

   msdb.dbo.sysjobs sj

  INNER JOIN

   msdb.dbo.sysjobsteps ss ON sj.job_id = ss.job_id

  ORDER BY

   sj.[name],

   ss.step_id

• mark lake

  Say Hey Kid

  Points: 702

  More actions

  July 11, 2006 at 2:08 am

  #648381

  I got that far... it was actually getting the DTS packages...

   

  which having opened my eyes I found in sysdtspackages.

   

  Sometimes my amazing skills of perception seem almost superhuman...

• Rich Holt

  SSC-Addicted

  Points: 438

  More actions

  July 11, 2006 at 10:13 am

  #648530

  Don't forget that DTS packages don't necessarily need to be stored in the database.  

  You can save a DTS Package as a structured storage file to a file system path accessible to the SQL Agent, and execute it from a scheduled job using the DTSRUN command (job step type CmdExec) and the "/F" argument.

  For example, if you had a DTS package named "FOO.dts", and saved it to the D: drive of the SQL Server, you could create a job with a CmdExec step like:

      dtsrun /F "D:\FOO.dts"

  This comes in handy when you have one job that needs to be executed on many SQL servers, and you don't want/need to use the system procs or Enterprise Manager to actually add the DTS to the SQL Server. 

  In my organization, we use this technique to simplify Release Management procedures.  By keeping the Structured Storage File (FOO.dts) under source control, the Release Team needs only to "get latest" from VSS to the SQL Server file system, and run a SQL script to create the job.  Makes for a clean and simple change control process.

  You can get the DTS package name from sysjobsteps (it's in the command):

  select job_id, step_id, step_name, command

    from msdb.dbo.sysjobsteps

   where subsystem = 'CmdExec'

     and command like '%dtsrun%'