In SQL Server 2016 you can customize the detail level of SSIS logging and create your own logging template. In this post I’ll show you how to implement this useful new feature.
Whenever you run an SSIS Package deployed to the SSISDB in Integration Services environment you need to submit a logging template. If you do not change the default setting the „basic“ level is used.
Up to Version 2014 there were 4 already defined templates (basic, performance, verbose and runtimeLineage). All of them give you different level of details.
The basic log gives you quite some information about how your packages are running and about warnings and errors. This template is still there and can of course still be used but it might not have all the information you’d like to see.
The other logging level are also still there and give you information about performance counter and run time or the source of the data rows in the tasks. The verbose level logs almost everything and it is hard to find the exact information you want to see.
A new wizard opens where you can see your already defined templates and create new ones.
To create a new one click on „Create“ and add a name and a description what for this Logging Level is meant to be.
Also you can use an already pre-defined logging level to extend statistics and events of the standard templates.
I use „basic“ and click on „ok“.
There are two other tabs „Statistics“ and „Events“. Here I can add and remove statistics from the „basic“ template.
An easy way to demonstrate this new feature is to add the „Component Data Volume Statistics“ to see a row counter and how many rows were transferred.
I now run my package with this customized Logging Level
To analyze these advanced logging information there is a table in the SSISDB „catalog. execution_data_statistics“.
I can afterwards query the results and logging details.
My package created 256 rows and sent them to the row counter destination.
When I query the statistics I see the tasks in the DataFlow-Component and that there were 256 rows transferred.
So I have a very easy way to extend the default logging with some information I am interested in.
Much fun playing around with this new feature in SQL Server 2016.