CODEWITHSUNDEEP
ssissql-agent-job
sandeep shetty
sandeep shetty

Reputation: 11

how to get SQL agent job name from a given SSIS package name?

I have package named "PROCESS_REPORT_SUBSCRIPTION_WEEKLY" . I need to list all the SQL agent jobs running this package.

Upvotes: 1

Views: 1533

Answers (1)

Pintu Kawar
Pintu Kawar

Reputation: 2156

You can query the msdb database objects.

select 
     a.job_id JobID
    ,a.name JobName
    ,js.step_id
    ,js.step_name
    ,js.subsystem
    ,b.name SSISPackageName
    ,js.command
 from  msdb.dbo.sysjobs  a 
Left Join msdb.dbo.sysjobsteps js on js.job_id=a.job_id 
left join msdb.dbo.sysssispackages b on a.name=b.name
Where b.name like '%MySSISPackageName%' OR js.command  like '%MySSISPackageName%'

Note:

...OR js.command  like '%MySSISPackageName%'

This code is to handle if package is executed with in T-SQL syntax.

Upvotes: 1

Related Questions