使用T-SQL获取SSIS包名、作业名和描述

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

我想从SQL Server中获取以下信息:

All SSIS Jobs Agent Names.
所有SSIS工作代理名称。
SSIS Package Name.
SSIS包名称。
[optional but nice to have] SSIS Job agent description.
[可选但很好]SSIS工作代理描述。

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

我可以用下面的表格把这3条信息都拉出来:

msdb.dbo.sysjobs gives me the Job name and Job description.
msdb.dbo。sysjobs给了我工作名称和工作描述。
msdb.dbo.sysssispackages gives me the SSIS Package name.
msdb.dbo。sysssispackages给出了SSIS包的名称。

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

不幸的是，我无法找到这两个表之间的相关性来将这3条信息整合到一起。

Any ideas? Thanks!

什么好主意吗?谢谢!

2 个解决方案

#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

问候,斯蒂芬

#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

#1