Using T-SQL to get the SSIS Package name, job name and description

Question

I'm trying to get the following information out of SQL Server:

• All SSIS Jobs Agent Names.
• SSIS Package Name.
• [optional but nice to have] SSIS Job agent description.

I'm able to pull all 3 of these pieces of information with no problems using the following tables:

• msdb.dbo.sysjobs gives me the Job name and Job description.
• msdb.dbo.sysssispackages gives me the SSIS Package name.

Unfortunately I'm not able to find a correlation between those 2 tables to gets me all 3 pieces of information together.

Any ideas? Thanks!

Answer 1

i also searched for an solution, this is working for me:

declare @MyJobTable table 
(
JobName nvarchar(255)
,StepName nvarchar(255)
,StepID int
,SSIDName nvarchar(255)
,StepCommand nvarchar(1024)
)

declare @MyCursor as cursor
declare @MyName as nvarchar(255)

set @MyCursor = CURSOR for select name from msdb.dbo.sysssispackages

open @mycursor
fetch next from @MyCursor into @MyName

while @@FETCH_STATUS = 0
begin 

insert into @MyJobTable
(JobName,StepName,StepID,SSIDName,StepCommand)
(select
jobs.name as JobName
,steps.step_name as StepName
,steps.step_id as StepID
,@MyName as SSIDName
,steps.command as StepCommand
from msdb.dbo.sysjobs as jobs
join msdb.dbo.sysjobsteps as steps
on jobs.job_id = steps.job_id
where steps.subsystem = 'SSIS'
and steps.command like '%'+@MyName+'%')
fetch next from @MyCursor into @MyName

end

select * from @MyJobTable
order by JobName,StepID

Regards, Stefan

Answer 2

try this,

select 
    --Job Information
     a.job_id
    ,a.name
    ,a.description
    --SSIS package Information
    ,b.name
    ,b.id
    ,b.description
    --Job steps Information
    ,js.step_id
    ,js.step_name
    ,js.subsystem
    ,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