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
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.
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.
In the properties dialog, you can find the server-wide 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.
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.
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.
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.
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:
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 or later, it’s a good idea to create custom logging levels to suit your needs.
Next Steps
- Take a look at the tip Integration Services Logging Levels in SQL Server 2016 to learn more about custom logging levels and the RuntimeLineage logging level.
- If you want to implement logging yourself, check out the tip Custom Logging in SQL Server Integration Services SSIS.
- For more SQL Server 2016 tips, you can use this overview.
