As 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
Comments