Thursday, February 2, 2012

Logging details from sysssislog

select Package,
StepId GroupId,
STUFF(CONVERT(CHAR(8), DATEADD(SECOND, ABS(TimeInMinutes), '19000101'), 8), 1, 2, CAST(TimeInMinutes / 3600 AS VARCHAR(12))) Duration,
TimeInMinutes TimeInSeconds,
from (
c.source Package,
DENSE_RANK() OVER (order by A.executionID ) StepID,
CONVERT(VARCHAR(100),DATEDIFF(SECOND,A.starttime,ISNULL(B.endtime,GETDATE()))) TimeInMinutes,
CASE WHEN B.endtime IS NULL THEN ' (running)' ELSE '' END AS [Status]
(select * from sysssislog where event = 'OnPreExecute') As A LEFT JOIN
(select * from sysssislog where event = 'OnPostExecute') As B ON A.sourceid = B.sourceid and A.executionid = B.executionid
LEFT JOIN (select * from
(select source,executionid,Row_Number() over (PARTITION by executionid order by executionid) RowId from sysssislog where event = 'OnPreExecute' )
t where RowId=1) C on C.source=A.source and C.executionid=a.executionid
) t
order by StepID,id

No comments:

Post a Comment