THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Andy Leonard

Andy Leonard is an author and engineer who enjoys building and automating data integration solutions. Andy is co-host of the Data Driven podcast. Andy is no longer updating this blog. His current blog is

Passively Mine SSIS Data Flow Rows Loaded From the SSIS Catalog

CatalogAs I wrote in Parsing SSIS Catalog Messages for Lookup Performance, v2:

I prefer to passively gather performance metrics – even if it’s a little convoluted and more work (for me). I don’t often need these metrics immediately available, so I execute queries similar to the query below and store the performance metrics in a table. I can (and do) build dashboards to track SSIS performance (perhaps I should blog about them…) using this passively-collected performance metrics.

As with Lookup Transformation messages, OLE DB Destinations in Data Flow Tasks record specific messages using the same format every time. Because of this (hard-coded) consistency, you and I can passively collect the number of rows written while executing packages in the SSIS Catalog using the (default) Basic logging level. We can use the following Transact-SQL query to collect this execution metadata post-execution:


declare @wroteString nvarchar(12) = '" wrote '
declare @rowsString nvarchar(12) = ' rows.'
declare @searchWroteString nvarchar(12) = '%' + @wroteString + '%'
declare @searchRowsString nvarchar(12) = '%' + @rowsString + '%'
declare @lenWroteString int = Len(@wroteString) + 1
declare @lenRowsString int = Len(@rowsString) + 1

  msg.operation_id As OperationID
, execution_path As ExecutionPath
, Substring([message]
, CharIndex('"'
                    , [message]) + 1
          , (CharIndex('"'
                     , [message]
                     , CharIndex('"'
                               , [message]) + 1))
                               , [message])) - 1) As OLEDBDestinationName
, SubString(
  , (PatIndex(@searchWroteString
            , [message])
    + @lenWroteString)
  , ((PatIndex(@searchRowsString
             , [message]))
            , [message])
     + @lenWroteString))
  ) As RowsLoaded
    [catalog].[event_messages] msg
Left Join [catalog].[extended_operation_info] info ON msg.extended_info_id = info.info_id
Where message_source_type = 40
  And message_type = 70
  And [message] Like N'%" wrote %[0-9]% rows%'
Order By msg.operation_id DESC


You might like working with Enterprise Data & Analytics because we instrument ETL.

Learn More:

Parsing SSIS Catalog Messages for Lookup Performance, v2
From Zero to Biml - 19-22 Jun 2017, London 
Designing an SSIS Framework (recording)
Biml in the Enterprise Data Integration Lifecycle (recording)
IESSIS1: Immersion Event on Learning SQL Server Integration Services – Oct 2017, Chicago

Published Thursday, May 18, 2017 10:57 PM by andyleonard

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS


No Comments

Leave a Comment


This Blog



My Latest Book:

Community Awards

Friend of Red Gate

Contact Me


Privacy Statement