By: Koen Verbeeck | Comments (2) | Related: > SQL Server 2016
Problem
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.
Solution
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:
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:
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:
In the pop-up window, you can see existing customized logging levels and you can create new ones or delete existing ones.
If you click Create, you can specify a name and a description:
In the Statistics pane, you can choose which kind of statistics are written to the SSISDB database, if any.
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.
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.
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....
In the pop-up dialog, you can choose from existing customized logging levels:
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.
If we modify the package slightly to generate a warning, you can now see only warnings popping up in the report:
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:
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.
Next Steps
- Try it out yourself! Download and install the SQL Server 2016 preview and follow the steps in this tip to create your custom logging level.
- For more info on the logging levels: Enable Logging for Package Execution on the SSIS Server.
- To understand why it is important to minimize logging overhead, read the article SSISDB: Why bigger isn’t always better and its follow-up post Avoid a big SSISDB by logging less…?.
- For more SQL Server 2016, read these other SQL Server 2016 Tips.
About the author
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
