SSIS Package and Task execution time queries from SYSSSISLOG

Some useful queries which helps to get package and task execution time from SYSSSISLOG table.

Query returns Package and Task execution time:

WITH Task_Details_CTE (ID,Source,SourceID,ExecutionID,StartTime,EndTime)

AS --provides the task exec. detail

(

SELECT sys1.ID, sys1.Source, sys1.SourceID, sys1.ExecutionID

, sys1.StartTime, sys2.EndTime

FROM dbo.sysssislog sys1

INNER JOIN dbo.sysssislog sys2

ON sys1.Event = 'OnPreExecute' --gets only the start time

AND sys2.Event = 'OnPostExecute' --gets only the completed time

AND sys1.ExecutionID = sys2.ExecutionID --groups package execution

AND sys2.SourceID = sys1.SourceID --associates identical tasks

)

SELECT --identifies the “package” tasks

CASE WHEN sys.SourceID = CTE.SourceID

THEN 'Package : ' +CTE.Source --+ ' (' + convert(varchar(30),sys.starttime,100) + ')'

ELSE '          ==> TASK: ' + CTE.Source

END AS [Package|Task] --indents tasks to package execs

, CTE.StartTime

, CTE.EndTime

, RIGHT('00' + CAST(DATEDIFF(SECOND,CTE.StartTime, CTE.EndTime)/60

AS VARCHAR(20)),3) + ' min.' +

RIGHT('0' + CAST(DATEDIFF(SECOND,CTE.StartTime, CTE.EndTime)%60

AS VARCHAR(20)),2) + ' sec.' AS TaskDuration

FROM dbo.sysssislog sys

INNER JOIN Task_Details_CTE CTE

ON sys.Event = 'PackageStart' --lists single row pkg exec.

AND sys.ExecutionID = CTE.ExecutionID

ORDER BY SYS.ID, CTE.ID, CTE.StartTime --orders by package, task

Retrieve Package Instance time:

WITH PkgStart AS (

    SELECT p.executionid,

           p.source,

           p.starttime,

           ROW_NUMBER() OVER(PARTITION BY p.executionid

                                 ORDER BY p.id ASC) AS rk

      FROM sysssislog p where event='OnPreExecute')

      ,PkgEnd AS (

    SELECT p.executionid,

           p.source,

           p.starttime,

           ROW_NUMBER() OVER(PARTITION BY p.executionid

                                 ORDER BY p.id DESC) AS rk

      FROM sysssislog p where event='OnPostExecute')

SELECT A.source

,DATEDIFF(MI,A.starttime,B.starttime) as 'Duration in Min'

,CONVERT(VARCHAR(8),A.starttime,108) 'Start Time'

,CONVERT(VARCHAR(8),B.starttime,108) 'End Time' 

FROM

(

SELECT s.*

FROM PkgStart s

WHERE s.rk = 1

) A INNER JOIN

(

SELECT e.*

FROM PkgEnd e

WHERE e.rk = 1

)  B ON A.executionid=B.executionid

ORDER BY  'Duration in Min' DESC

Retrieve all Package Instances and Tasks:

SELECT

B.source 'Source'

,DATEDIFF(MI,A.starttime,B.starttime) as 'Duration in Min'

,DATEDIFF(SS,A.starttime,B.starttime) as 'Duration in Sec'

,CONVERT(VARCHAR(8),A.Starttime,108) 'Start Time'

,CONVERT(VARCHAR(8),B.starttime,108) 'End Time'

FROM

(select executionid,source,starttime from sysssislog where event like 'OnPreExecute' ) A

INNER JOIN

(select executionid,source,starttime from sysssislog where event like 'OnPostExecute' ) B

ON A.source=B.source AND A.executionid=B.executionid

ORDER BY

A.starttime

Getting execution time for Package by Package:

This example shows for Package name called Package1

SELECT B.source 'Soure'

,DATEDIFF(MI,A.starttime,B.starttime) as 'Duration in Min'

,CONVERT(VARCHAR(8),A.starttime,108) 'Start Time'

,CONVERT(VARCHAR(8),B.starttime,108) 'End Time'

FROM

(select executionid,source,starttime from sysssislog where event like 'OnPreExecute' ) A

INNER JOIN

(select executionid,source,starttime from sysssislog where event like 'OnPostExecute' ) B

ON A.source=B.source AND A.executionid=B.executionid

WHERE B.source like 'Package1'

ORDER BY a.starttime

Cheers!

uma