This is a short script to help the DBA with documentation purposes.  This would come in handy especially in those cases where you are consulting or you have taken on a new job.

Suppose you want/need to find out what SQL Agent jobs are running SSIS packages.  This script will help to quickly identify those jobs.

Code block

SELECT DISTINCT j.name
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobsteps js
ON j.job_id = js.job_id
WHERE js.subsystem = 'SSIS'
OR js.command LIKE '%DTEXEC.exe%'