sqlvisual-studiossisssmssql-server-agent

Find SQL Server Job Related to Existing SSIS Package in Visual Studio

I've started working in the middle of an existing project and I want to identify the Job in SSMS which is running an existing package in visual studio (SSIS). Is there a way to do that or only searching job by job?

The project has several jobs so I was wondering if there's an easier way to identify the job that is running a specific SSIS package.

Thank you in advance,

Solution

  • The following script will return all SQL server jobs, and the packages that they run. If your package store isn't SSIDB\Live you will need to change this value for your own environment.

    select 
      j.name AS job
    , s.step_name as step
    , s.command
    , SUBSTRING((SUBSTRING(s.command,(CHARINDEX('SSISDB\Live\',s.command)+
        LEN('SSISDB\Live\')),len(s.command))),0,(CHARINDEX('.dtsx',(SUBSTRING(s.command,(CHARINDEX('SSISDB\Live\',s.command)+LEN('SSISDB\Live\')),len(s.command)))))+5) as package
from 
    msdb.dbo.sysjobsteps s
    inner join msdb.dbo.sysjobs j
        on s.job_id = j.job_id and s.subsystem ='SSIS'