Monday, 31 August 2015

SQL Server 2016 CTP 2.3 Custom Logging Levels

Case
In CTP 2.3 Custom logging levels where added. How does it work?

Solution
You can set it up in the Catalog with SQL Server Management Studio.

1) Customized Logging Level
Right click on the SSISDB Catalog and choose Customized Logging Level. A new window will be opened where you can add and change Customized Logging Levels.
Right click the SSISDB Catalog
















2) Create
Click on the create button to add a name and description of your new Customized Logging Level. In this case we will create a logging level that only shows OnError events.
Only OnError





















3) Edit
A new logging level has been created. You can click it to view and change the properties. Our first Customized Logging Level has ID 1.
Edit Customized Logging Level


















4) Statistics
Go to the second tab to change the Statistics. In CTP 2.3 the checboxes on the individual rows seems to be missing. So I added them manually to the screenshot, but they do work when you use your Arrow keys to select the cell where they supose to be and then press [Space] to (un)check the individual item.

Select Statistics options



















5) Events
Now go to the third tab to select the events you want to log. Same problem with the missing checkboxes here, but the same workaround works. In this case I only checked OnError events. When you're ready click on the Save button to save all changes. 
Select the Events


















6) Execute
Now right click on your package to execute it and go to the Advanced tab to select the Logging Level. In the drop down list select the bottom item <Select customized logging level...>.
Select customized logging level...

















7) Select customized logging level
A new popup window appears when you can select your newly created Cusomized Loging Level. Again there seems to be missing a checkbox here. By default the row seems to be already selected, so you don't have to selected it. If you do try to select it, you will uncheck it and you will get a warning:
No customized logging level is specified. Please select a customized logging level value before performing this operation.





















8) View details customized logging level
When you click on the button with the three dots on the right side of the popup window, you will be able to see the details. Here the checkboxes are visible!
View details of your customized logging level




















9) Execution results
Now it's time to see the result. As you can see: a customized logging level was selected and only errors are shown.


















10) Feature request
Besides the hidden checkboxes I also would like to make my Customized Logging Level the default for the catalog. At the moment this seems not to be possible.
Default logging level























Update: Nice youtube video with Matt Masson showing the custom logging levels

No comments:

Post a Comment

Please use the SSIS MSDN forum for general SSIS questions that are not about this post. I'm a regular reader of that forum and will gladly answer those questions over there.

All comments are moderated manually to prevent spam.