CODEWITHSUNDEEP
sqlvisual-studiossisssmssql-server-agent
TiagoD
TiagoD

Reputation: 19

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,

Upvotes: 0

Views: 1496

Answers (2)

Paul Hough
Paul Hough

Reputation: 1

A bit more general that does not need the SSIS Catalog structure:

SELECT j.[name] AS JobName
    , s.step_name AS StepName
    , s.Command
    , REVERSE(SUBSTRING(SUBSTRING(REVERSE(s.command), CHARINDEX('xstd.', REVERSE(s.command)) + 5, LEN(s.command)), 1
        , CHARINDEX('\', SUBSTRING(REVERSE(s.command), CHARINDEX('xstd.', REVERSE(s.command)) + 5, LEN(s.command))) - 1)) AS Pack
FROM msdb.dbo.sysjobsteps s
    INNER JOIN msdb.dbo.sysjobs j ON s.job_id = j.job_id AND s.subsystem ='SSIS' 
;

Upvotes: 0

A.Steer
A.Steer

Reputation: 409

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'

Upvotes: 1

Related Questions