Thursday, December 19, 2013

Find table used in agent jobs step with SSIS

Find table that been used inside SSIS package by using system table.

Query
SELECT
      job.name
,     job.description
,     jobstep.step_name
,     [SSIS Path] = folder.foldername + '\' + package.name
FROM  sysjobs job
      JOIN sysjobsteps jobstep
      ON job.job_id = jobstep.job_id
      AND jobstep.subsystem = 'SSIS'
      AND (jobstep.command LIKE '%/SERVER ' + @@SERVERNAME + '%'
            OR jobstep.command LIKE '%/SERVER "(local)"%'
            OR jobstep.command LIKE '%/SERVER "."%')
      JOIN sysssispackagefolders folder
      JOIN sysssispackages package
      ON folder.folderid = package.folderid
      ON jobstep.command LIKE '%' + folder.foldername + '\' + package.name + '%'
WHERE CAST(CAST(packagedata AS VARBINARY(MAX)) AS VARCHAR(MAX)) LIKE '% TABLE NAME %'
      and job.enabled = 1

No comments:

Post a Comment