Administering SSIS: Parsing SSIS Catalog Messages for Lookups

“The SSIS Catalog is a database and an application.” – Andy Leonard, circa 2015

If there’s one thing I want you to get from this post, it’s this: The SSIS Catalog is a database and an application. While it’s a pretty cool database and application, it isn’t anything different from the databases and applications you support every day. As such, you can query and extend the database just like any other.

One of the coolest features of the SSIS Catalog is the logging. You can select between four options:

  1. None
  2. Basic (the default)
  3. Performance
  4. Verbose

The text of log messages are stored in the SSISDB database, in the internal.operation_messages table. The catalog.operation_messages view queries the internal_messages table.

To parse a message string contained in the SSIS Catalog, I can use a T-SQL script similar to the one shown below. It will grab messages generated by the Lookup transformation from the catalog.operation_messages view and display some useful metrics. If I want to isolate the results to one execution of one SSIS package, I supply a value for the @operation_id parameter. If @operation_id is NULL (as shown below), all LookUp transformation messages will be parsed.

This script does not account for NULLs,  division by zero, or partial data. It provides some handy metrics you will want to monitor as part of your enterprise Data Integration Lifecycle Management (DILM).

Use SSISDB
Go

declare @LookupStringBeginsWith varchar(100) = 'The Lookup processed '
declare @LookupStringBeginsWithSearchString varchar(100) = '%' + @LookupStringBeginsWith + '%'
declare @ProcessingTimeString varchar(100) = 'The processing time was '
declare @ProcessingTimeSearchString varchar(100) = '%' + @ProcessingTimeString + '%'
declare @CacheUsedString varchar(100) = 'The cache used '
declare @CacheUsedSearchString varchar(100) = '%' + @CacheUsedString + '%'
declare @operation_id bigint = NULL

Select
  operation_id
, Substring(message,
            (PatIndex(@LookupStringBeginsWithSearchString, message) + Len(@LookupStringBeginsWith) + 1 ),
             (
              (CharIndex(' ',
                         message,
                         PatIndex(@LookupStringBeginsWithSearchString, message) + Len(@LookupStringBeginsWith) + 1))
              -
              (Patindex(@LookupStringBeginsWithSearchString, message) + Len(@LookupStringBeginsWith) + 1)
             )
            ) As LookupRowsCount
, Substring(message,
            (PatIndex(@ProcessingTimeSearchString, message) + Len(@ProcessingTimeString) + 1 ),
             (
              (CharIndex(' ',
                         message,
                         PatIndex(@ProcessingTimeSearchString, message) + Len(@ProcessingTimeString) + 1))
              -
              (PatIndex(@ProcessingTimeSearchString, message)+ Len(@ProcessingTimeString) + 1 )
              )
             ) As LookupProcessingTime
, Convert(
          bigint,
          Substring(message, (Patindex(@LookupStringBeginsWithSearchString, message) + Len(@LookupStringBeginsWith) + 1 ),
                    (
                     (
                       Charindex(' ',
                                 message,
                                 Patindex(@LookupStringBeginsWithSearchString, message) + Len(@LookupStringBeginsWith) + 1)
                      )
                     -
                      (Patindex(@LookupStringBeginsWithSearchString, message)+ Len(@LookupStringBeginsWith) + 1 )
                     )
                   )
         )
          /
         Convert(Numeric(3, 3),
                 Substring(message,
                           (
                            Patindex(@ProcessingTimeSearchString, message) + Len(@ProcessingTimeString) + 1),
                           (
                            (Charindex(' ',
                                       message,
                                       Patindex(@ProcessingTimeSearchString, message) + Len(@ProcessingTimeString) + 1
                                      )
                            )
                             -
                            (
                             Patindex(@ProcessingTimeSearchString, message) + Len(@ProcessingTimeString) + 1
                            )
                           )
                          )
                ) As LookupRowsPerSecond
, Substring(message,
            (
             Patindex(@CacheUsedSearchString, message) + Len(@CacheUsedString) + 1 ),
             (
              (Charindex(' ',
                         message,
                         Patindex(@CacheUsedSearchString, message) + Len(@CacheUsedString) + 1
                        )
             )
              -
             (
              Patindex(@CacheUsedSearchString, message) + Len(@CacheUsedString) + 1
             )
            )
           ) As LookupBytesUsed
, Convert(bigint, Substring(message,
                            (
                             Patindex(@CacheUsedSearchString, message) + Len(@CacheUsedString) + 1 ),
                            (
                             (
                              Charindex(' ',
                                        message,
                                        Patindex(@CacheUsedSearchString, message) + Len(@CacheUsedString) + 1
                                       )
                             )
                              -
                             (
                              Patindex(@CacheUsedSearchString, message) + Len(@CacheUsedString) + 1
                             )
                            )
                           )
         )
        /
         Convert(bigint,
                 Substring(message,
                           (
                            Patindex(@LookupStringBeginsWithSearchString, message) + Len(@LookupStringBeginsWith) + 1 ),
                            (
                             (Charindex(' ',
                                        message,
                                        Patindex(@LookupStringBeginsWithSearchString, message) + Len(@LookupStringBeginsWith) + 1
                                       )
                             )
                            -
                             (
                              Patindex(@LookupStringBeginsWithSearchString, message) + Len(@LookupStringBeginsWith) + 1
                             )
                            )
                           )
                ) As LookupBytesPerRow
From catalog.operation_messages
Where message_source_type = 60 -- Data Flow Task
  And message Like @LookupStringBeginsWithSearchString
  And operation_id = Coalesce(@operation_id, operation_id)

While this is not production-ready code, you may be able to use it to glean insight into SSIS performance metrics and to learn more about SSIS Catalog internals.

Enjoy!

Learn more:
Watch the Video
Advanced SSIS Training with Tim Mitchell and Andy Leonard in Dallas, TX 9-11 Mar 2015
Linchpin People Blog: SSIS
Stairway to Integration Services
Test your knowledge

:{>

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. :{>

One thought on “Administering SSIS: Parsing SSIS Catalog Messages for Lookups

  1. Ehy, I’ve just published a set of scripts I’ve using in the last months on GitHub:
    https://github.com/yorek/ssis-queries, where I did something similar.
    I’ll publish a post on in today or tomorrow. Why don’t you fork it and add your scripts? It will be a great SSIS queries repository! 🙂

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.