SSISDB ERD – A Journey to DB deployment automation
Posted by kulmam92 on June 17, 2013
I decided to reverse engineer the SSISDB to have a better understanding of the SSIS catalog.
Reza Rad posted a really good article called “SSIS 2012 Catalog series“. However, I thought it would be better to create my own version of an ERD to have better understanding of it. Here’s what i came up with.
Yon can download the pdf version of this from SSISDB_ERD
Object related tables
There are four object tables: folder, environment, project and package. I think you can easily figure out the purpose of each table. Other than these key tables are the tables for relationship (environment_reference), parameters, versions and permissions. SSISDB keeps a project’s previous versions as binary data in the internal.object_versions table. You can specify how many versions that you want to keep by using the properties of an SSIS catalog.
This table contains one row for each operation in the Integration Services catalog. You can track all logical operations that were performed on the server using this table, such as project deployment or package execution. Below are the operation_type values from BOL:
|operation_type Value||operation_type Description||object_id Description||object_name Description|
|1||Integration Services initialization||NULL||NULL|
|2||Retention window (SQL Agent job)||NULL||NULL|
|3||MaxProjectVersion (SQL Agent job)||NULL||NULL|
|101||deploy_project (Stored procedure)||Project ID||Project name|
|106||restore_project (Stored procedure)||Project ID||Project name|
|200||create_execution and start_execution (Stored procedures)||Project ID||NULL|
|202||stop_operation (Stored procedure)||Project ID||NULL|
|300||validate_project (Stored procedure)||Project ID||Project name|
|301||validate_package (Stored procedure)||Project ID||Package name|
|1000||configure_catalog (Stored procedure)||NULL||NULL|
[executions] has a one-to-one relationship with [operations], which means the [execution] table is a subset of the [operations] table. If you look at the ERD closely, the relationship from operation to execution is made only when the operation type is 200 – create_execution and start_execution (Stored procedures). This means only operation_type value 200 will generate a record in the execution table.
If you want to know which package in a project was executed, you can use the following query. This is the creation script of the [catalog].[executions] view.
opers.[operation_guid] as [dump_id],
FROM [internal].[executions] execs INNER JOIN [internal].[operations] opers
ON execs.[execution_id]= opers.[operation_id]
LEFT JOIN [internal].[operation_os_sys_info] ossysinfos
ON ossysinfos.[operation_id]= execs.[execution_id]
WHERE opers.[operation_id] in (SELECT id FROM [internal].[current_user_readable_operations])
OR (IS_MEMBER(‘ssis_admin’) = 1)
OR (IS_SRVROLEMEMBER(‘sysadmin’) = 1)
Message related tables
If you want to check the execution log of a package, these are the tables that you need to query. [operation_messages] and [event_messages] have a one-to-one relationship similar to [operation] and [event]. [event_messages] is a subset of [operation_messages]. The below is the source code of the [catalog].[event_messages] view.
Declare @ExecutionID NVarChar(max)
SELECT opmsg.[operation_message_id] as [event_message_id],
WHEN (opmsg.message_source_type = 10) THEN ‘ISServerExec’
WHEN (opmsg.message_source_type = 20) THEN ‘Transact-SQL stored procedure’
FROM [internal].[operation_messages] opmsg LEFT JOIN [internal].[event_messages] eventmsg
ON opmsg.[operation_message_id] = eventmsg.[event_message_id]
WHERE opmsg.[operation_id] = @ExecutionID
If you execute an SSIS package deployed to a SSIS catalog and it fails, you will see a message similar to the following.
Executed as user: NT Service\SQLSERVERAGENT. Microsoft (R) SQL Server Execute Package Utility Version 11.0.2100.60 for 32-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 4:09:59 PM Package execution on IS Server failed. Execution ID: 120092, Execution Status:4. To view the details for the execution, right-click on the Integration Services Catalog, and open the [All Executions] report Started: 4:09:59 PM Finished: 4:10:27 PM Elapsed: 28.641 seconds. The package execution failed. The step failed.
You will need to use either a prebuilt standard report or query the SSISDB tables to get a detailed error message. The following screenshot shows where you can locate that report. If you decide to query the SSISDB, you may want to consider using the SSIS Reporting Pack developed by Jamie Thomson.
Here I list the tables and its definitions copied from BOL.
|internal||catalog_encryption_keys||Contains SSISDB encryption key|
|internal||catalog_properties||Contains the properties of the Integration Services catalog.|
|internal||data_type_mapping||Contains SSIS data type and SQL data type mapping|
|internal||environment_references||Contains the environment references for all projects in the Integration Services catalog.|
|internal||environment_variables||Contains the environment variable details for all environments in the Integration Services catalog.|
|internal||environments||Contains the environment details for all environments in the Integration Services catalog. Environments contain variables that can be referenced by Integration Services projects.|
|internal||event_messages||Contains information about all messages passed during events like OnWarning, OnError|
|internal||executable_statistics||Contains Detailed execution log for each executable|
|internal||executables||Contains Information about each executable object in packages
For example : Package itself, Execute SQL Task, ETC.
|internal||execution_component_phases||Contains Detailed execution log for each data flow component.|
|internal||execution_data_statistics||Contains Number of rows sent through data flow and few other details|
|internal||execution_data_taps||Contains information about data taps
Data Taps are new features in SSIS 2012 which provides ability to log whole data stream in one of data flow’s data paths. and the best part of story is that you can add and remove data taps to a package at the time of execution
|internal||execution_parameter_values||Contains the actual parameter values that are used by Integration Services packages during an instance of execution.|
|internal||execution_property_override_values||Contains information about property values that set at the time of execution of package and overrides default values.|
|internal||executions||Contains the instances of package execution in the Integration Services catalog. Packages that are executed with the Execute Package task run in the same instance of execution as the parent package. With every execution of package, an Execution_id will be assigned|
|internal||extended_operation_info||Contains extended information for all operations in the Integration Services catalog.|
|internal||folders||Contains the folders in the Integration Services catalog.|
|internal||object_parameters||Contains the parameters for all packages and projects in the Integration Services catalog.|
|internal||object_versions||Contains the versions of objects in the Integration Services catalog. In this release, only versions of projects are supported in this view.|
|internal||operation_messages||Contains messages that are logged during operations in the Integration Services catalog.|
|internal||operation_os_sys_info||Contains system information like cpu count, available physical memory and total physical memory at the time of operation|
|internal||operation_permissions||Contains the effective permissions for the current principal for all objects in the Integration Services catalog.|
|internal||operations||Contains the details of all operations in the Integration Services catalog. Each operation can be applied to an object and have a status as a result.|
|internal||packages||Contains the details for all packages that appear in the Integration Services catalog.|
|internal||projects||Contains the details for all projects that appear in the Integration Services catalog.|
|internal||validations||Contains the details of all project and package validations in the Integration Services catalog.|