In my ongoing series on ETL best practices, I recently wrote about the importance of logging in extract-transform-load processes. For users of later versions (2012 and beyond) of SQL Server Integration Services, adding logging to those ETL processes is very simple. Since logging is managed by the catalog, adding logging to an ETL process is a one-step process and requires no modifications to the package itself. However, these built-in logging levels don’t offer much flexibility of what is logged. Starting with SQL Server 2016, that process is made more flexible with the addition of SSIS custom logging levels.
In this post, I will introduce SSIS custom logging levels and briefly show how they are used.
Logging in the Catalog
Logging the execution of a package stored in the SSIS catalog couldn’t be easier. The catalog has four built-in logging levels to choose from: Basic (the default), Performance, Verbose, and None. These logging level determine how much information is logged when the package is executed. When executing a package, you can specify one of these logging levels as an execution parameter. If no logging level is specified, the default logging level of the catalog is automatically used.
Unlike prior versions of SSIS, adding logging to an existing package requires no modifications to that package; just choose your logging level at runtime and the catalog does the rest.
It works the same way when executing via T-SQL. The LOGGING_LEVEL system parameter is used to define how packages are logged for a single execution.
As I mentioned, this couldn’t get simpler. However, for that simplicity, you pay the price of flexibility. Those predefined logging levels are provided as-is, and cannot be modified. In SSIS 2012 and 2014, a catalog execution meant that you had to had to accept what was logged by those system logging levels, or build your own logging infrastructure (including tables, stored procedures, etc.) yourself.
Starting with SSIS 2016, that limitation is no longer there. SSIS custom logging levels were introduced as a way to allow customization while still using the catalog for logging.
SSIS Custom Logging Levels
When using SSIS 2016, you’ll notice a new option named Customized Logging Level in the right-click menu:
Navigating there opens up the Customized Logging Level Management window. This interface is used to create new logging levels, or modify or delete existing ones.
As shown, there are currently no custom logging levels in this catalog. To create one, click the Create button. Here you will give the logging level a name and an optional description. You can also set whether this new logging level should use an existing system logging level as a template. In the example below, I am creating this with the events included in logging level of Verbose.
Once created, I use the Statistics tab to change the statistical information I want to gather. As shown, since I chose Verbose as a template, everything is included by default on this new logging level.
Similarly on the Events tab, everything is checked from the use of Verbose as a template. For this new logging level, I have unchecked a couple of events that I don’t care to log.
Using SSIS Custom Logging Levels
Now that I have created this new logging level, I can use it for logging packages executed in the SSIS catalog. As shown in the Execute Package window below, one of the logging options is to select a custom level.
Choosing this option opens up a simple dialog box allowing me to select which custom logging level to use. I’ll select the one I just created.
Now when I click through to execute this package, it will use the settings from this new custom logging level.
The T-SQL code equivalent is shown below. Note that I have to set log level to 100 to indicate the use of a custom logging level. Also note the new system parameter CUSTOMIZED_LOGGING_LEVEL which expects the name of the custom logging level.
Logging Level Management
Custom logging levels are specific to each catalog. Each level is created once in the SSIS catalog and can be used for any package execution in that catalog.
Behind the scenes, custom logging levels are stored in the table [internal].[customized_logging_levels]. Note the events_value column which stores a coded value defining the events to be logged.
Because the logging levels are stored as rows in the SSISDB database (as are most catalog settings), logging levels should be portable from one catalog to another via T-SQL script. I’ve done some brief testing on portability and found it to work as intended. Since this falls outside the controls of the catalog API, be sure to check your results when moving custom logging levels from one catalog instance to the other via T-SQL.
Conclusion
Logging package executions in the SSIS catalog was already easy but a bit inflexible. SSIS custom logging levels improve on this by providing both ease of use and flexibility. By creating custom log levels, you have the ease of one-step logging with the ability to pick and choose which events are to be logged.
Do we still need a script component to measure pipeline throughput like MeanRPS, MaxRPS, MeanBytesPerSec, Max/MinBytesPerSec (like the ProjectReal ETL implementation did back in 2005) or, is it possible to do with a T-SQL statement against SSISDB?
I don’t believe rows per second is explicitly logged, but the details of that information can be captured (depending on which log setting you choose) and then mean, max, etc. could be calculated using those details.
Tim, have you done testing on the impact of adding/subtracting different events to log?
I’m looking for best practices recommendation on creating custom logging events. I rarely use SSISDB reporting because I use BiXpress so much of this is being double logged. Where SSISDB logging comes handy is if the package won’t syntax check then Bixpress can’t log it so its useful to have validation events in SSISDB.
Hi Gary, I’ve done some testing between the built-in levels (Basic, Performance, and Verbose). The logging levels above Basic, especially Verbose, do add some measurable overhead on the SSIS server, particularly in disk I/O. For the custom logging levels, I haven’t done a lot of performance testing on an event-by-event basis. Possibly a topic for a future blog post, though.