SSIS Catalog Logging Tables

Making the most of the SSIS catalog requires an understanding of how to access the information stored in the logging tables. Although there are built-in reports to show this information, there are limitations in their use. Fortunately, the logging tables in the SSIS catalog database are (mostly) straightforward and easy to understand once you’ve worked with them a bit.

In this post, I’ll list and describe the purpose of the essential SSIS catalog logging tables.

SSIS Catalog Logging Basics

When the SSIS catalog was introduced with the release of SQL Server 2012, it fundamentally changed the way SSIS package executions are logged. Gone was the need go through tedious pages of UI checkboxes to capture ETL status, runtimes, and informational messages; the logging of these events happens automatically by default.

The particulars of logging could now be changed as a runtime setting without modifying or even opening the package. By using one of four predefined logging levels, defining the depth of how much information to log became as simple as choosing a selection from a drop-down list. Using built-in SSIS catalog logging required surrendering granular control over which logging events you wanted to capture, but it was a small price to pay for the ease of catalog logging.

Built-In Reports

One of the easily recognizable advantages of SSIS catalog logging is the inclusion of built-in reports that present package execution information in an easy-to-digest format. Using these reports, one can see a snapshot of what’s happening right now, get an overview of prior executions, or drill into a specific SSIS load for troubleshooting purposes.

However, in using the built-in SSIS catalog reports, there are a few shortcomings:

• The user running the reports must have a specific set of permissions beyond simple read-only access to the SSSDB database
• The built-in reports are hard-coded and cannot be modified
• The reports are only available from within SQL Server Management Studio

The good news is that all the data shown in the packaged catalog reports is simply stored in logging tables in the SSIS catalog database (SSISDB). With a basic understanding of the underlying logging tables, one can write queries or build their own customized reports for displaying status and history information.

SSIS Catalog Logging Tables

There are several dozen tables in the SSISDB database, but you only need to understand a handful of them to be proficient at package execution analysis. Below I list and explain some of these essential logging tables.

[internal].[operations]

The operations table captures each high-level operation that occurs in the SSIS catalog. Here you’ll find meta information such as the operation ID, when the operation began and when it completed (if it did), the status of the operation, and who performed the operation. Note that this doesn’t just store SSIS package executions; also written to this table is a log of project deployments, project restores, changes to global properties of the catalog, and package validations, among other operations. This table joins to other logging tables on its object_id column.

The status values are displayed by numerical ID, but oddly, there is no lookup table in the SSISDB database to show which IDs translate to which status values. You’re welcome to borrow my script in which I manually create a status lookup table for ease of reporting.

Operations are logged to this table regardless of which logging level you choose (even if you pick the logging level “None”).

[internal].[executions]

This table lists each of the package executions that has been created in the SSIS catalog. Information in this table includes the execution ID, the path to the package, the catalog environment (if one was used for this execution), and whether the 32-bit runtime was used for the execution. All of the information about start and end time and execution status for an execution is stored in the [internal].[operations] table listed above. The execution_id column in the executions table joins the operation_id column in the operations table to match up the execution to its proper operation log.

It is important to note that there are a couple of execution types that will not show up in the executions table:

• Packages executed using the Execute Package Task in SSIS. Packages executed using this task are still logged, but each of their tasks and components is logged as part of the parent package and does not show a distinct execution record in [internal].[executions].
• Packages executed in SQL Server Data Tools (SSDT). Because a package execution within SSDT is not truly a catalog execution, it is not logged in the catalog logging tables. Only package executions invoked from the SSIS catalog are logged in these tables.

The executions table will be used in most every query you write against the SSISDB database. The execution object is the centerpiece of such reporting, so this table will be a key part of the solution. Get it know it well.

Like the operations table, records are written to this table regardless of which logging level – even None – is selected.

[internal].[executable_statistics]

This table lists the execution summary for each executable; that is, every task or container in an executed SSIS package. Here you’ll find the full path to the executable object within the package, start and completion times, duration, and the execution result (stored as a simple 1 or zero for failure or success, respectively). This table is useful for identifying task-level failures or bottlenecks.

Like the previous two tables, you’ll find log information in here for every execution regardless of the logging level specified.

[internal].[execution_component_phases]

This table is useful for capturing runtime statistics about data flow components. The execution_component_phases table logs each execution phase of every data flow component, showing very granular information about each step (or phase) of the component execution. This can be a very busy table, especially if you’ve got a lot of data flow components or you have a data flow running many times within a loop.

Runtime data is logged to this table only in Performance or Verbose logging modes.

[internal].[execution_data_statistics]

This table shows row counts of data moved through an SSIS data flow. Each “leg” of the pipeline will be represented here, with each row in the log table indicating the source component, destination component, when the log entry was created, and how many rows were processed. Keep in mind that each row in this log table shows a single buffer of data, so for a large load you’ll have multiple log entries per leg of the pipeline.

This log table is only written to when using Verbose logging mode.

[internal].[operation_messages] and [internal].[event_messages]

I’ve grouped these tables together because they are typically used together. The operation_messages table contains the text of detailed messages in the log, while event_message stores some of the metadata belonging to that message. The operation_messages table captures information about package executions, validations, and a few other operations (such as restoring an older version of a project), while event_message is specific to package executions and logs information about the package itself.

These logging tables are used for package executions set to any logging level except None.

[internal].[event_message_context]

The event_message_context table logs many of the implicit runtime settings for packages and connections. Many of the settings from the package properties are logged here, as well as the details from each package and project connection. This table is useful for debugging, or for checking for packages that are not in compliance with company guidelines (such as setting a specific Package Protection Level).

Log information is written to this table when using either the Basic or Verbose log. Curiously enough, executing a package using Performance logging mode excludes this table from the log output.

[internal].[execution_parameter_values]

The execution_parameter_values table logs the parameters passed to a package execution. Both package and project level parameters are stored here, as well as system parameters that may be implicitly or explicitly specified. This log table is useful for tracking down why a package failed by analyzing the runtime values it was passed (another great reason to parameterize your packages).

One of the security features of SSIS is that it masks sensitive pieces of information. That includes this logging table, which has an encrypted column for storing passwords or any other runtime parameter explicitly marked as Sensitive.

Runtime parameter values will be written to this table for any package execution in the catalog, regardless of the logging level specified.

[internal].[validations]

The validations table stores the results of validation operations that are performed on packages or projects. If you have as part of your execution or workflow a process to run validations against your SSIS packages, you should include this table in your logging reports.

Final Thoughts

Since some of the log tables depend on the logging level selected, I included which of the logging levels use each table above. However, if you are using SQL Server 2016, remember that you can create your own custom log level. In that case, you could build your own custom logging scheme to capture as little or as much of this detail as you want.

In summarizing the above, I didn’t reference the SSIS catalog views that are typically used to abstract the logging information. Each of the tables I mentioned above has a view of the same name (except that it will reside in the [catalog] schema) that provides similar information to what you’ll find in the table. However, each of the catalog views contains in its WHERE clause a restriction on which data will be returned depending on the user account querying the view. Although I get why it was designed this way, it requires granting catalog access (in addition to database access) even for those who will monitor package execution. If you need this row-level security in the SSIS catalog logs, use the views. Otherwise, accessing the tables is a more flexible option.

Conclusion

In this post, I have listed and described the function of each of those key logging tables. Getting to know the critical logging tables in the SSIS catalog will make monitoring and troubleshooting packages much easier.