Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Integration Services Logging Levels in SQL Server 2016


By:   |   Read Comments (2)   |   Related Tips: More > SQL Server 2016

Quickly Resolve Performance Problems for IIS, .NET and SQL Server       >>>   Get Started


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:

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.

Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Sunday, September 03, 2017 - 1:12:50 PM - Koen Verbeeck Back To Top

Hi Dory,

I have no idea. I use SSMS 17 myself without any issue (althoug I don't connect to SSIS 2014. Maybe you can add a comment on the download page?

https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms


Wednesday, August 30, 2017 - 7:54:19 PM - Dory O Back To Top

 Using SSMS 17 & SS 14, any reason why Customized Logging Level would appear on the SSIDB context menu, but not be selectable?


Learn more about SQL Server tools