Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Logging Level Recommendations for the SQL Server Integration Services Catalog


By:   |   Read Comments (4)   |   Related Tips: More > Integration Services Development

Attend a SQL Server Conference for FREE >> click to learn more


Problem

In the Integration Services (SSIS) catalog, there are several options for the built-in logging. These options are called logging levels and they control the granularity of the logging when SSIS packages run in the catalog. Which logging level should we choose for which scenario?

Solution

With the introduction of Integration Services (SSIS) 2012 a couple of years ago, the project deployment model was introduced. Along with this model came the SSIS catalog: a central repository for storing, configuring and executing packages. The catalog has several interesting features. One of them is the built-in logging, which stores information about various events and statistics in the SSISDB database. This information is exposed through views and through pre-built reports.

SSIS Catalog views

In this tip, weíll give an overview of the available logging levels and use cases for each one of them. Important to know is that there is a default logging level you can configure for the entire SSIS catalog. You can find it by right-clicking on the catalog and selecting Properties.

SSIS Catalog properties - Description: SSIS Catalog properties

In the properties dialog, you can find the server-wide default logging level.

SSIS Catalog Default Logging Level

SSIS Logging Levels

The following logging levels are currently present in the catalog (as of SQL Server 2016 SP1 CU2):

  • None
  • Basic
  • Performance
  • Verbose
  • RuntimeLineage

Notice itís also possible to create your own custom logging levels, where you choose the events and statistics to be logged. This process is described in the tip Integration Services Logging Levels in SQL Server 2016.

None

This logging level is quite easy to understand: it doesnít log anything at all. Use this logging level if performance of your SSIS packages is of utmost importance. With the logging removed, you shave off a bit of overhead that is typically associated with logging.

If you do want some logging to take place Ė for instance the start and end times of the package or the numbers of rows transferred Ė youíre responsible yourself for implementing this in the packages itself. You can find an example in the tip Custom Logging in SQL Server Integration Services SSIS.

Basic

The basic logging level is the default logging level right after you enable the SSIS catalog in the SQL Server database engine. However, basic is not the best term there is, since quite a lot of information is being logged with this logging level. Itís about the same information when you ran a package inside Visual Studio and you look at Execution Results tab.

SSIS test package execution results

The screenshot above is from a simple package with one dataflow, with one source and destination. Already quite a bit of information is displayed. If we look at the relevant catalog view for an execution of the same package in the catalog, we can see 21 rows are being logged.

relevant catalog view for an execution of the same package in the catalog

In most cases, you donít need this much information, so itís a good idea to change the default logging level to another logging level. If you use SQL Server 2016, you can create a custom logging level to minimize the overhead. In previous versions, youíre better off with the Performance logging level (which is discussed in another section).

The basic logging level logs the following information:

  • Executable Execution Statistics Ė information about the execution (time and result) of the tasks in the control flow
  • All events except OnProgress, OnCustomEvent, Diagnostic, DiagnosticEx, NonDiagnostic.

If you want to retrieve the number of rows written by a destination, you could try to find the relevant OnInformation event in the event_messages catalog view and parse the message to find the number of rows.

number of rows written in the SSIS catalog basic

The basic logging level does not log the number of rows written to a separate table.

Performance

The performance logging level logs the following information:

  • Executable Execution Statistics Ė information about the execution (time and result) of the tasks in the control flow
  • Component Execution Statistics Ė information about the execution time of the transformations in the control flow
  • Errors and warnings

This logging level is intended to be used when you are performance tuning a package, since it logs all the execution times of the different objects. However, in SQL Server 2012/2014 itís a suitable alternative for the basic logging level since less events are being logged. The exception is the case when you have many data flows with many transformations; here the overhead of logging all the execution statistics might be considerable.

Keep in mind the Performance logging level does not log the number of rows written to a destination.

Verbose

This logging level is easy to explain: it logs everything. All statistics and events are logged into the catalog. This means there is considerable overhead associated: a simple package can already generate dozens of pages of logging. Only use this logging level for diagnostic purposes, when you need all the possible information to troubleshoot an issue.

This is the only built-in logging level that logs the number of rows written to a destination. Itís logged in a per-buffer basis in the execution_data_statistics catalog view:

rows sent via SSIS verbose logging level

In SQL Server 2012/2014, itís your only option to get the row numbers logged into the catalog. However, due to the considerable overhead itís a better idea to log the rows yourself into another table using the rowcount transformation.

RuntimeLineage

As explained in the tip Integration Services Logging Levels in SQL Server 2016, the RuntimeLineage logging level is intended for tracking lineage, probably for a future feature that hasnít been announced yet. Please refer to the tip for more info on this logging level.

Unless you want to build your own lineage tracking solution Ė on top of undocumented behavior- you donít need to use this logging level.

Conclusion

There are different logging levels in the SSIS catalog that determine the granularity of the logging. If you use SQL Server 2012 or 2014, you are most likely better off using the Performance logging level, as it typically has the least overhead. If you use SQL Server 2016, itís a good idea to create custom logging levels to suit your needs.

Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Tuesday, April 25, 2017 - 9:32:10 AM - Koen Verbeeck Back To Top

Hi Djad,

I'm not exactly a clustering expert, but from what I've seen is that SSIS is typically outside the cluster (most likely because it was not cluster aware in most versions).


Monday, April 24, 2017 - 4:53:41 PM - Djad Back To Top

 good afternoon,

 

i'm doing a migration from Stand alone server to a cluster active pasive setup.

Stand alone contain(database Engine, repoting services, and integration services)  I have seperated the reporting services from the Cluster because i can not have it in the cluster. how about the the integration services shall i do the same thing or i would keep it inside the cluster? what will be your segusstion. I will love to hear from you

thanks

 

 

 


Wednesday, April 19, 2017 - 9:12:49 AM - Koen Verbeeck Back To Top

Hi Christian,

the OnVariableValueChanged event is available for custom logging levels, so yes, it should be possible.


Tuesday, April 18, 2017 - 2:30:21 AM - Christiann Back To Top

 Hi,

thanks for this. Is there a way to retrieve variable changes in this log?

BR

Christian 


Learn more about SQL Server tools