CODEWITHSUNDEEP
ssisssrs-2008monitoring
unknown
unknown

Reputation: 69

SSIS performance monitoring using SSRS

I want to know the top slowest packages using SSRS. I don't know in which table I can find the duration of every package. Actually sysssislog stores the start/end time of package components but I don't know how can I handle with it. Could you help me find the appropriate table or even the sql query to use ?

Upvotes: 0

Views: 334

Answers (1)

billinkc
billinkc

Reputation: 61239

The dbo.sysssislog is only going to store the start/stop times if you've enabled SQL Server logging.

If you have, then I'd be lazy and start with some the queries built out in the SSIS Performance Framework. They were built out for 2005 so you'll need to change the references from sysdtslog90 to sysssislog. Also, if you are following those queries, you would want to log more events that just start/stop but the basic logic is sound.

If you just want plain and simple, then you would write a query like this to get started.

WITH STARTS AS
(
    -- Find the execution ids for all the start events
    SELECT
        S.executionid
    ,   S.starttime
    ,   S.source
    FROM
        dbo.sysssislog AS S
    WHERE
        S.event = 'PackageStart'
)
, STOPS AS
(
    -- Find the execution ids for all the start events
    SELECT
        S.executionid
    ,   S.starttime
    ,   S.source
    FROM
        dbo.sysssislog AS S
    WHERE
        S.event = 'PackageEnd'
)
SELECT
    A.source AS PackageName
,   A.starttime AS StartTime
,   COALESCE(B.starttime, CURRENT_TIMESTAMP) AS PackageEndTime
,   DATEDIFF(mi, a.starttime, COALESCE(B.starttime, CURRENT_TIMESTAMP)) AS PackageDuration_M
FROM
    STARTS A
    -- Lots of reasons there may not be an end time
    LEFT OUTER JOIN
    STOPS B
        ON A.executionid = B.executionid;

Upvotes: 1

Related Questions