What Events are Included in the SSIS Catalog Log Levels

Logging Levels

Packages run through the SSIS Catalog can automatically have their logs captured in SQL Server 2012. The SSIS Catalog provides four different log levels – None, Basic, Performance, Verbose. The default log level is Basic.

The table below shows which SSIS events are captured in the [catalog].[operation_messages] view in the four logging levels.

 

Level Events Notes
None None Captures enough information to say whether the package succeeded or failed, and does not log any messages to the [operation_messages] view.
Basic OnPreValidateOnPostValidate

OnPreExecute

OnPostExecute

OnInformation

OnWarning

OnError

Captures similar information to what is displayed on the console by the default when a package is run with dtexec.
Performance OnWarningOnError This log level is required to track the performance information for the run (how long it took to run each task / component, etc) but does not log all of the events captured by the Basic log level.
Verbose All events The Verbose log level captures all log events (including performance and diagnostic events). This logging level can introduce some overhead on performance – see details blow.

Note that additional information is captured and stored in other tables / views in the SSIS Catalog. For an overview of the Views related to package execution, see Jamie Thomson’s blog post – SSIS Logging in Denali.

When to Use Each Logging Level

None

While the None logging level provides slightly better performance than Basic, I don’t expect that it will be used too often in production environments. This logging level does not capture error and status messages, which makes diagnosing problems difficult. However, if you have Packages Never Fail ™, this logging level might be for you! (Hey, you can always re-run the package with a higher level if something goes wrong…)

Basic

This is the recommended logging level for every day use. It captures important events (like errors and warnings), as well as enough progress information to display what the currently active task is, and how long each task has taken (internally this information is stored in the [catalog].[executable_statistics] view).

Performance

The Performance log level should be used when you are doing benchmarking and performance tuning for your packages. While it actually logs less messages to the [catalog].[operation_messages] view than Basic, it captures a lot more events internally to analyze the performance of the data flow components. As a result, there is a bit more overhead during execution – packages run with Basic will actually run a little faster than Performance (in this case Performance means “give me all of the performance details”, not “run my packages as fast as you can”).

Verbose

The Verbose logging level captures a lot of events (especially when using OLE DB connections, as they generate many DIAGNOSTIC events). Verbose should only be used when trying to debug or diagnose package failures.

Custom Log Events

The only log level that captures custom log events (i.e. special events that are raised with custom / 3rd party SSIS extensions or scripts) is Verbose. Due to the overhead that the Verbose level introduces, I recommend creating your own custom reports if you need to capture these custom events.

8 Responses to “What Events are Included in the SSIS Catalog Log Levels”

  1. [...] SSMS show a breakdown of task and component performance of a package execution when you run with a logging level of [...]

  2. Andrej Kuklin says:

    Hi Matt,

    can I somehow change what events are being logged for some logging level? For instance, I'd like to combine 'Basic' logging and timings from the Performance logging. I'd like to use the SSISDB Catalog functionality, i.e. I don't want to roll out some custom logging infrastructure. Thanks

  3. mattmasson says:

    Hi Andrej,

    Unfortunately we don't currently support this functionality, but I can see it being something we add in the future.

  4. Andrej Kuklin says:

    Hi Matt,

    thanks for answering. Another cool feature would be changing the log level on per package basis. Like I start the execution with Basic logging, but please do Verbose logging for Package1 and Package2 (cause they are more error-prone and tend to break or more mission-critical and I need all possible debug information if they do break). This seems to be not possible right now

  5. [...] What Events are Included in the SSIS Catalog Log Levels [...]

  6. marv says:

    How do i set the Catalog Logging level using dtexec? /r doesn't seem to be doing it.

  7. [...] be limited to debugging and troubleshooting.  Matt Mason wrote a blog about logging levels here, and he literally wrote the [...]

Leave a Reply

You must be logged in to post a comment.