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:

Use SSISDB
go

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

Select
  msg.operation_id As OperationID
, execution_path As ExecutionPath
, Substring([message]
          , CharIndex(‘"’
                    , [message]) + 1
          , (CharIndex(‘"’
                     , [message]
                     , CharIndex(‘"’
                               , [message]) + 1))
                       –
                      (CharIndex(‘"’
                               , [message])) – 1) As OLEDBDestinationName
, SubString(
    [message]
  , (PatIndex(@searchWroteString
            , [message])
    + @lenWroteString)
  , ((PatIndex(@searchRowsString
             , [message]))
     –
     (PatIndex(@searchWroteString
            , [message])
     + @lenWroteString))
  ) As RowsLoaded
From
    [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

Enjoy!

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

Andy Leonard

andyleonard.blog

Christian, husband, dad, grandpa, Data Philosopher, Data Engineer, Azure Data Factory, SSIS guy, and farmer. I was cloud before cloud was cool. :{>

Comments

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.