How to find which SSIS packages are called by which jobs

• VG-619426

  Hall of Fame

  Points: 3914

  More actions

  March 18, 2010 at 12:03 pm

  #145955

  Hi,

  I am in the process of getting all the SSIS packages, which are active i.e they are called by jobs or exes. I don't see any sys procedures/tables for this. I don't think so, we have log for all the SSIS packages. I do get list of all the SSIS packages from sysdtspackages90, but I want the only ones, which are executed.

  Thanks,

  VG

• Elliott Whitlow

  SSC Guru

  Points: 102402

  More actions

  March 18, 2010 at 12:33 pm

  #1136210

  Take a look at:

  SELECT j.name,

  js.step_id,

  js.step_name,

  js.command

  FROM msdb.dbo.sysjobs j,

  msdb.dbo.sysjobsteps js

  WHERE j.job_id = js.job_id

  AND js.subsystem = 'SSIS'

  CEWII

• VG-619426

  Hall of Fame

  Points: 3914

  More actions

  March 18, 2010 at 4:26 pm

  #1136366

  Wow, just perfect for me...just added more where condition to this...

  SELECT j.name,

  js.step_id,

  js.step_name,

  js.command

  FROM msdb.dbo.sysjobs j,

  msdb.dbo.sysjobsteps js

  WHERE j.job_id = js.job_id

  --just added this get ssis packages called from command line

  AND (js.subsystem = 'SSIS' or command like '%dtexec%') and j.enabled = 1

  Thank you very much!!!

  VG

• Elliott Whitlow

  SSC Guru

  Points: 102402

  More actions

  March 18, 2010 at 4:58 pm

  #1136384

  Great, glad to be of assistance.

  CEWII