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 a Data Philosopher at Enterprise Data & Analytics, an SSIS Trainer, Consultant, developer of the Data Integration Lifecycle Management (DILM) Suite, a Business Intelligence Markup Language (Biml) developer and BimlHero; SQL Server database and data warehouse developer, community mentor, engineer, and farmer. He is a co-author of SQL Server Integration Services Design Patterns, and the Stairway to Integration Services. Keep up with Andy, join his mailing list!

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

:{>

Published Friday, January 16, 2015 8:00 AM 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

 

Davide Mauri said:

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! :)

January 19, 2015 5:31 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

News

My Latest Book:

Community Awards



Friend of Red Gate

Contact Me

Archives

Privacy Statement