获取其他包调用的所有包 [英] get all packages called by other packages
问题描述
我正在尝试列出所有活动的SSIS包。
我正在使用SSIS 2008,因此我没有SSIS目录,只有MSDB。
我可以检索SQL代理作业调用的包的数据。
我想让所有包在其他包中执行,由那些父包调用(使用执行包任务编辑器)。
怎么能我得到有关他们的信息? (名称,父包名称,上次运行日期等)
也许我可以使用我的MSDB包中的XML代码来完成它,或者它们可能会出现在"运行包"下面。历史表,如果存在?
谢谢,
Arie
主包打电话给几个孩子包含
的软件包
执行套餐任务。
以下
代码可以帮助你。声明@MyJobTable表
(
JobName nvarchar(255)
,StepName nvarchar(255)
,StepID int
,SSIDName nvarchar(255)
,StepCommand nvarchar(1024)
)
将@MyCursor声明为游标
将@MyName声明为nv archar(255)
为msdb.dbo.sysssispackages中的选择名称设置@MyCursor = CURSOR
打开@mycursor
从@MyCursor获取下一个到@MyName
而@@ FETCH_STATUS = 0
begin
插入@MyJobTable
(JobName,StepName,StepID,SSIDName,StepCommand)
(选择
jobs.name作为JobName
,steps.step_name作为StepName
,steps.step_id作为StepID
,@ MyName作为SSIDName
,steps.command作为StepCommand
来自msdb.dbo.sysjobs作为工作
加入msdb.dbo.sysjobsteps作为步骤
on jobs.job_id = steps.job_id
其中steps.subsystem ='SSIS'
和steps.command喜欢'%'+ @ MyName +'%')
从@MyCursor获取下一个到@MyName
结束
从@MyJobTable中选择*
按JobName订购,StepID
跨度>
Hi,
I'm trying to make a list of all my active SSIS packages.
I'm using SSIS 2008, thus I don't have SSIS catalogs, only MSDB.
I can retrieve data of my packages called by SQL agent jobs.
I would like to get all packages executed within other packages, called by those parent packages (which use execute package task editor).
How can I get information about them? (names, name of parent package, last run date, etc)
Perhaps I can do it using XML code from my MSDB packages, or maybe they will appear under "running packages" history table, if such exists?
Thank you,
Arie
master package calling a couple of child packages with the Execute Package Task.
following code may help you.
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
这篇关于获取其他包调用的所有包的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!