DBA > Articles

Integration Services Logging Levels in SQL Server 2016

By: Koen Verbeeck
To read more DBA articles, visit http://dba.fyicenter.com/article/

At the time of writing, SQL Server 2016 preview (CTP 2.3) has been released and there are some changes for the Integration Services (SSIS) Catalog logging levels. This tip will explain what is new and how you can use it.

Integration Services Logging Levels
In SQL Server 2012, the SSIS catalog was introduced. This catalog provides a central administration platform for the SSIS environment. One of its features is the logging levels, where each logging level specifies which events are logged to the SSIS catalog. Do not confuse this with the log providers of an SSIS package. The log providers log events to a specific destination: SQL Server, a flat file, an XML file and so on. A logging level is a configuration option for the built-in logging of the SSIS catalog: every time you run an SSIS package in the catalog, events are logged to tables in the SSISDB database.

In its initial release and in SQL Server 2014, Integration Services had 4 logging levels:
None - logging is turned off.

Basic - all events are logged, except custom and diagnostic events. The name is quite misleading, as a lot of information is logged. This is the default logging level of the SSIS catalog.

Performance - only performance statistics and the OnWarning and OnEvent are logged. This logging level might log less information than the basic logging level, but it depends on the number of data flows and their complexity. Data flow performance information of this logging level is shown in the catalog report Execution Performance.

Verbose - all events are logged. Only use this logging level for advanced troubleshooting or fine tuning.

In the SQL Server 2016 preview, a new logging level has been added and there is now the possibility to create your own custom logging levels. Both will be explained in the following sections.

SQL Server 2016 preview
As mentioned earlier, at the time of writing SQL Server 2016 is still in preview (at the time of writing CTP 2.3 has been released). This means that the features of Integration Services and its catalog can still change and that functionality might change, disappear or be added.

The RuntimeLineage Logging Level
Currently, not a lot of information is out there on this logging level. The documentation states: Collects the data required to track lineage in the data flow. My guess is this logging level might be used in other features/products to track the lineage of the data; i.e. where does the data come from and where does it go to? In order to test this logging level, I created a very basic package:

Full article...

Other Related Articles

... to read more DBA articles, visit http://dba.fyicenter.com/article/