Logging Level Recommendations for the SQL Server Integration Services Catalog

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


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?


The information provided in this tip is valid for Integration Services versions 2012 and up. It applies to on-premises installations of SSIS, but also for SSIS catalogs in Azure (for more information, see the tip Configure An Azure-SSIS Integration Runtime).

With the introduction of Integration Services (SSIS) 2012 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 present in the catalog (new levels might be added in later versions. This list is valid for at least until SQL Server 2017):

  • 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.


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.


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 or later, you can create a custom logging level to minimize the overhead. In previous versions, you might be 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.


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.


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.


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.


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 or later, itís a good idea to create custom logging levels to suit your needs.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on MSSQLTips.com. Koen has been awarded the Microsoft MVP data platform award for many years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

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

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 (55151)

 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





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

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 (55003)


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



get free sql tips
agree to terms