THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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.

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

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

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

News

My Latest Book:

Community Awards



Friend of Red Gate

Contact Me

Archives

Privacy Statement