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.
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.
- BI xPress has an auditing framework and SSIS monitoring console that gives you a BIDS like view at runtime
- Enabling logging on your packages and use the sample reports
- Use a custom logging framework (1, 2, 3, 4, 5, 6)
- Run the packages with a custom application and capture events at runtime (similar to DTLoggedExec)
- View execution logs using BI System Monitor and the SSIS Log Analyzer
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.
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.
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
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.