包执行情况

select b.source as package,a.source as task,a.starttime,a.endtime,right(convert(varchar(19),a.endtime - a.starttime,120),8) duration
from  
(select  source,executionid,sourceid,MIN(ID) id,min(event) event,min(starttime) starttime,max(endtime) endtime
from dbo.sysssislog  
where starttime>='20130916'
group by source,sourceid,executionid ) a 
left join
(select source,executionid 
from dbo.sysssislog 
where starttime>='20130916' and event ='Packagestart' 
) b on a.executionid=b.executionid
order by package,starttime


--ETL执行时长
select source,DATEDIFF(MINUTE,MIN(starttime),max(endtime)) as duration
from dbo.sysssislog l
inner join msdb.dbo.sysssispackages p on l.source = p.name
inner join msdb.dbo.sysssispackagefolders f on p.folderid = f.folderid and f.foldername='SSISFolderName'
where starttime>='2013-11-15 00:00:00' --and starttime<'2013-11-11 09:00:00'
group by source
order by duration desc