miércoles, 6 de abril de 2016

Integration Services Logging Levels in SQL Server 2016

SQL Server

https://www.mssqltips.com/sqlservertip/4034/integration-services-logging-levels-in-sql-server-2016/

Escenario

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.


Configuration

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:
Simple Data Flow
It reads data from a table, adds a custom column, merges streams and writes the data to a table. When the package is executed, we can see in the catalog view catalog.event_message_context that extra information about the dataflow has been logged:

Select Chart Type
In the screenshot, you can observe three things:
  • the source is the dbo.DimCustomer table
  • the destination is the dbo.DimCustomer table (in another database)
  • a column named "TestColumn" is added in a Derived Column with the expression "abc"
Using this information, you can determine where all of the data comes from and where it goes to.

Creating a Custom Logging Level

A very welcome addition to the SSIS catalog is the ability to create your own logging levels. With such a logging level, you can determine which statistics and events are logged to the SSISDB database. For example, as noted before, the basic logging level already logs quite a lot of information. If you are interested in warnings and errors only, it's interesting to create your own logging level. To create one, right-click on the SSIS catalog and choose Customized Logging Levels:
Customized Logging Levels
In the pop-up window, you can see existing customized logging levels and you can create new ones or delete existing ones.
Customized Logging Levels Configuration
If you click Create, you can specify a name and a description:
Create a new Customized Logging Level
In the Statistics pane, you can choose which kind of statistics are written to the SSISDB database, if any.
Choose statistics
The most interesting pane, Events, allows you to choose which events are logged, similar to the SSIS logging providers in a package. In our custom logging level, we only need the OnWarning and OnError events.
Choose events
Every time you execute a package, you can choose which logging level it has to use. By default, this is the basic logging level. If we execute a package that runs successfully, we can see it logs quite a lot of rows, some of which might not be interesting.
Basic logging: too many rows
Luckily, the drill-through Messages report in the SSIS catalog has a separate section for error messages. However, if you are interested in warnings, you either have to search through all those messages, or you need to use the filter to find them. Now let's run the package with the new custom logging level. In the Execute Package dialog, you need to go to the Advanced pane and choose Select customized logging level....
Execute package, choose logging level
In the pop-up dialog, you can choose from existing customized logging levels:
Choose custom level
Clicking the ellipsis will give you more information about that particular logging level. Now, when we execute the package, we can now see logging has been greatly reduced.
Less logging!
If we modify the package slightly to generate a warning, you can now see only warnings popping up in the report:
Only warnings
Unfortunately it is currently not possible to make a customized logging level the default level for the SSIS catalog. This means you have to set it each time you execute a package. Fortunately you can specify a custom level in a SQL Server Agent job step:
SQL Agent
Note it is possible to create a custom logging level using the stored procedure catalog.create_customized_logging_level.

Conclusion

SSIS adds more flexibility to its logging levels in the catalog by introducing a new logging level - Runtimelineage - and by giving the opportunity to create your own customized logging levels. The Runtimelineage level will probably be used in dynamic lineage models in the future. The custom levels are very useful as they allow you to choose which events are to be logged.