business_intelligence_sql_server_ssas_ssis_ssrs

Simple SSIS package monitoring for SQL Server 2008, Part One

Business Intelligence analyst have been using SSIS for almost a decade now and yet we still have no centralized place to answer some very common operating questions.  Such as…

  • How many packages do we have?
  • How often are they run?
  • How are they configured?
  • How are these packages performing over time?

Microsoft recently addressed this in their latest version of SQL Server 2012 by introducing the SSIS Catalog and the new Execution and Logging reports.  They enable a fast way to visualize and troubleshoot performance.

SSISExecutionAndLoggingReports

But what about those of us still using SQL Server 2008 or 2005?  Are we left without a way to visualize our package environment? In short, yes. There are no native Microsoft supported options. However, multiple third-party projects have been developed that target this necessity.  Matt Mason does a solid job listing them here.

Unfortunately, all of the options above involve editing your existing packages to include additional code or worse — convincing your development team to adopt a new framework.   This could take time and – depending on your solution – substantial cost.

The following is a simple approach to package monitoring, which is easy to implement.  It requires only that your package log two event types: OnError and OnInformation. Once your packages have those check-boxes marked, the logging system is set.

Capture1
Want to create an external switch to turn this logging on and off? Take a look at these… [1] [2].

Packages with these two events turned on will log messages to the database of your choice.   Row counts plus duration equal much win. If you’ve never used logging, it takes a few clicks, look here.  Once you’ve kicked off a few packages with logging turned on, your sysssislog (sysdtslog90 for 2005) table will have some entries. Give it a SELECT * and you’ll see why most people turn to third-party solutions…

The sysssislog is a mess.  Some of this stems from Microsoft’s decision to use a bubble-up message style which causes messages to repeat themselves as they travel up from the inner-most components in your package to the top level. Have a dataflow inside a sequence container? Triplicate messages.  No worries though.  Using a bit of creative querying we can cut through the mess and get most of the important stuff into a nice compact view.

SimpleSSISQuery

The query at the bottom of this page will yield the result set above. It is a one-row-per-run executive overview.  You can use it to trend execution history and performance.  It is sorted by time, with the most recent package runs up top.   It will work against ANY database that has a sysssislog table, but sysssislog tables which contain the  OnError and OnInformation events will let you get the cool stuff. Stuff like the ConfigMessage, the PackageRows, and the Status.  Feel free to checkmark and log as many events as you like, but be careful. Excessive logging incurs an I/O penalty and can quickly bloat the log table. Use the messages column to keep a cap on how mouthy your packages get; ensuring a lean log and happy DBAs. Download the query below.  

Or, feel free to copy, paste, and execute.

/**
Author: Troy Witthoeft
Date: 2014-01-30
Description: SSIS log package overview query.
**/

-- The following CTE "numbers off" the packages inside a single executionid.
-- The numbers are ordered by time.
;WITH MultiPackageFinderCTE AS (
    SELECT DISTINCT executionid, sourceid, source, MIN(starttime) AS MinStartTime, computer,
        ROW_NUMBER() over (PARTITION BY executionid ORDER BY MIN(starttime) DESC) AS PackageOrdinal
    FROM dbo.sysssislog
    WHERE sourceid IN (SELECT DISTINCT sourceid FROM dbo.sysssislog WHERE event = 'PackageStart')
    GROUP BY executionid, sourceid, source, computer
)
  
--Main Query
SELECT * FROM (
    SELECT A.executionid, A.computer, A.operator, A.starttime,
        A.Duration, A.endtime, A.Messages, A.MessageSources,
        COALESCE(B.PackageID,A.executionid) AS PackageID, B.PackageName, B.ExecutionNo,
        C.PackageStart, D.PackageEnd, D.datacode,
        E.ErrorCount,  F.FirstError, G.ConfigMessage, H.PackageRows,
        Status = ( CASE
            --Infer a status using PackageStart, PackageEnd, the endtime, and OnError messages.
            WHEN PackageStart IS NOT NULL AND PackageEnd IS NOT NULL AND ErrorCount IS NULL THEN 'Success'
            WHEN PackageStart IS NOT NULL AND PackageEnd IS NULL AND ErrorCount IS NULL AND endtime > DATEADD(s,-10,GETDATE()) THEN 'Executing'
            WHEN PackageStart IS NOT NULL AND PackageEnd IS NULL AND ErrorCount IS NULL AND endtime < DATEADD(s,-10,GETDATE()) THEN 'Stalled'            
            WHEN PackageStart IS NOT NULL AND ErrorCount > 0 THEN 'Failure'
            WHEN PackageStart IS NULL AND PackageEnd IS NULL AND ErrorCount IS NULL THEN 'Config Success'
            WHEN PackageStart IS NULL AND PackageEnd IS NULL AND ErrorCount > 0 THEN 'Config Failure'
            ELSE 'Other'
        END )
    FROM (
        SELECT executionid
            ,MAX(computer) AS computer
            ,MAX(operator) AS operator
            ,MIN(starttime) AS starttime
            ,DATEDIFF(second, MIN(starttime), MAX(endtime)) As Duration
            ,MAX(endtime) AS endtime
            ,COUNT(message) AS Messages
            ,COUNT(DISTINCT sourceid) AS MessageSources
        FROM dbo.sysssislog
        GROUP BY executionid
    ) AS A
  
    LEFT JOIN (
        SELECT executionid
            ,sourceid AS PackageID
            ,source AS PackageName
            ,ROW_NUMBER() OVER (PARTITION BY computer, sourceid ORDER BY MinStartTime) AS ExecutionNo
        FROM MultiPackageFinderCTE
    ) AS B
    ON A.executionid = B.executionid
  
    -- Get the PackageStart event time.
    LEFT JOIN (
        SELECT executionid
            ,starttime AS PackageStart
        FROM dbo.SysSSISLog
        WHERE event = 'PackageStart'
    ) AS C
    ON A.executionid = C.executionid
  
    -- Get the PackageEnd event time.
    LEFT JOIN (
        SELECT executionid
            ,datacode
            ,endtime AS PackageEnd
        FROM dbo.SysSSISLog
        WHERE event = 'PackageEnd'
    ) AS D
    ON A.executionid = D.executionid
  
    -- Count the error messages.
    LEFT JOIN (
        SELECT executionid
            ,COUNT(message) AS ErrorCount
        FROM dbo.SysSSISLog
        WHERE event = 'OnError'
        GROUP BY executionid
    ) AS E
    ON A.executionid = E.executionid
  
    -- Promote the first error message inside the executionid.
    LEFT JOIN (
        SELECT executionid
            ,message AS FirstError
        FROM (
            SELECT executionid
                ,message
                ,ROW_NUMBER() OVER(PARTITION BY executionid ORDER BY starttime ASC) AS ErrorNumber
            FROM dbo.sysssislog
            WHERE event = 'OnError'
            ) AS F1
        WHERE ErrorNumber=1
    ) AS F
    ON A.executionid = F.executionid
  
    -- Get the configuration message.
    -- Optional: If using eternal XML configs,
    -- uncomment the subtring method to parse out the UNC path.
    LEFT JOIN (
        SELECT executionid
            ,message as ConfigMessage
            --,SUBSTRING(message, charindex('configure from the XML file "', message) + 29, charindex('".', message) - charindex('configure from the XML file "', message) - 29) AS dtsConfig
        FROM dbo.SysSSISLog
        WHERE event = 'OnInformation'
        AND message LIKE '%package%attempting%configure%'
        GROUP BY executionid, message
    ) AS G
    ON A.executionid = G.executionid
  
    -- Get package-level OnInformation messages having the words "wrote rows"
    -- Extract integers from these messages. Sum them per executionid.
    LEFT JOIN (
        SELECT executionid
              ,SUM(ISNULL(CONVERT(INT, SUBSTRING(message, charindex('wrote ', message) + 5, charindex('rows.', message) - charindex('wrote ', message) - 5)),0)) As PackageRows
        FROM dbo.SysSSISLog
        WHERE event = 'OnInformation'
        AND message LIKE '%wrote%rows%'
        AND sourceid IN (SELECT DISTINCT sourceid FROM dbo.sysssislog WHERE event = 'PackageStart')
        GROUP BY executionid
    ) AS H
    ON A.executionid = H.executionid
) AS X
  
ORDER BY starttime DESC

Updates
1-10-2014 – Added MessageSources to identify the number of components actively logging messges. Adjusted the CTE distinguish between packages sharing an executionid as separate events.

Like this? Part two of this series builds up this query to create an SSRS dashboard to monitor our SSIS environment.

7 thoughts on “Simple SSIS package monitoring for SQL Server 2008, Part One”

Leave a Reply

Your email address will not be published. Required fields are marked *