Tuesday, 22 September 2015

Query - To list the jobs and their steps from where SSIS packages are getting executed- SQL 2008

Recently, I was asked to write a SQL query to fetch the information of the enabled jobs and their steps where SSIS package (stored in SQL server) is getting called directly.

As most of us know information related to jobs and other SQL agent related stuff is stored in the MSDB database in SQL server. There are quite a few tables which comes handy while trying to fetch the job details and honestly, I did not know where do I look for the required information. While browsing for the data I got to know about the following tables which provides all the necessary information 

  • sysjobsteps - Stores the job step details 
  • sysjobs - Stores the information related to jobs

Note - I noticed that when the package (stored in SQL server) is called from the job step, "command" column in the sysjobsteps starts with "/SQL". Following query is based on this observation only and I could be wrong in assuming that hence please feel to correct me in case I'm wrong. Also if there exists an easier way to find the job-package details please let me know..  

SQL Script

J.name AS JobName
CHARINDEX('"\',SJ.command,1) + 2,
CHARINDEX('"\',SJ.command,1) + 2,
) AS PackageName
FROM msdb..sysjobsteps SJ
INNER JOIN msdb..sysjobs J
ON SJ.job_id = J.job_id and J.enabled = 1 and SJ.command like '/SQL%'

No comments:

Post a Comment