After a short hiatus, the 30 Days of SSIS series is back! Today’s special guest post is from Jamie Thomson .
--------------------------------------------
SSIS Catalog Log Tables
As you may already have gleaned from earlier posts in the 30daysofSSIS series SSIS in Denali includes the new SSIS Catalog that is used for execution and administration of SSIS projects and their associated paraphernalia. An important new feature provided by the SSIS Catalog is the automatic logging of all package execution activity along with some built-in reports that provide a view over that log activity. One good example of such a report is the “All Executions” report that gives an overview of all executions of packages in a given folder:
All of the underlying data for this and other reports exists in tables in a database called [SSISDB] and this of course means that you also have the ability to build your own reports atop the same data. The volume of data that is available for reporting is determined by the LOGGING_LEVEL at which your packages are executed; the available LOGGING_LEVELs are:
- None
- Basic
- Performance
- Verbose
One notable improvement from earlier versions of SSIS regarding LOGGING_LEVEL is that it is configured externally from a package at execution-time. This means that if you need to increase the LOGGING_LEVEL (perhaps for problem investigation) you can do so without having to make changes to your packages.
If you want to build your own reports then you will need to familiarize yourself with the following views, all of which exist in a schema called [catalog]:
| View | Description |
| executions | Contains a record for every instance of a package being executed on the SSIS Catalog |
| event_messages | All events that are captured from executing packages. [event_messages] is most analogous to the [sysssislog] table that was provided in earlier versions of SSIS. |
| event_message_context | Provides contextual information about a subset of records in [event_messages] |
| executable_statistics | Every package, container or task that executes during a package execution has a record here providing duration and execution result (success or otherwise) |
| execution_parameter_values | The inputs provided to each execution. If you want to know what LOGGING_LEVEL was used for an execution you will find that in [execution_parameter_values]. |
| execution_component_phases | Provides information that enables determination of bottlenecks in dataflows which is invaluable for performance tuning. Specifically it tells us how long each component in the dataflow spends in its various phases of execution; those phases are {Validate, PrepareForExecute, PreExecute, AcquireConnections*, PrimeOutput, ProcessInput, PostExecute, ReleaseConnections*, Cleanup} |
| execution_data_statistics | Provides information about the number of rows processed by each component in a dataflow. If you find the rowcounts depicted in a dataflow when it executes in the development environment useful then [execution_data_statistics] is for you. |
*AcquireConnections and ReleaseConnections phases are not logged in Denali CTP3 however they will be added later.
Integrating Your Custom Logging Infrastructure
Many of you will have existing logging frameworks that you have built for previous versions of SSIS and you will be happy to know that you are allowed to integrate those frameworks into [SSISDB] by creating your own tables without invalidating your SQL Server license. Moreover, in a later release of SQL Server Denali (its not available in CTP3 unfortunately) the [execution_id] that uniquely identifies each execution in [catalog].[executions] will be available in a system variable called @[System::ServerExecutionID].
You will be able to use this value in your own tables to link your custom logging framework to the built-in log tables in the SSIS Catalog.
Reporting Services Project Template
You can build reports atop the SSIS Catalog using any reporting tool you wish though of course many will choose to use SQL Server Reporting Services (SSRS). In order to kickstart your report authoring I have provided a SSRS project template that provides some shared datasets that may prove useful:
You can download this template as a zip file from here , note that it will currently only work with Denali CTP3. The Shared Data Source “SSISDB.rds” currently points to (localhost) so simply change that to point to your [SSISDB] of choice and away you go.
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson
https://twitter.com/jamiet