Searching SQL Jobs for Package References

Did you ever find a lone package and wonder whether it's used by a SQL Agent job?

This query will show which, if any, job steps use the package. It can also be used to search job text for any particular string you wish to find (table name, sqlcmd usage, etc.)

USE msdb

DECLARE @filter VARCHAR(1024)

-- include the dtsx extension to search for a package

SET @filter = '%DailyLoad%.dtsx%'

---- we can also look for a particular table or any

---- other value we might expect to find in a job step

SET @filter = '%DatabaseQueue%'

---- what might we be using for SQLCMD?

SET @filter = '%sqlcmd -S%'

SELECT

NAME,

step_name,

subsystem,

command

FROM

sysjobsteps AS ss

INNER JOIN sysjobs AS sj

ON ss.job_id = sj.job_id

WHERE

command LIKE @filter

I did a quick search for '%DatabaseBackup%' to get the following result: