I can speak SQL

Fluent SQL speaker

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.

SSISDB ERD

SSIS ERD

Yon can download the pdf version of this from SSISDB_ERD

Table

Main tables

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.

image

[Operations]

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]

[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.

image

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.

SELECT     execs.[execution_id],
execs.[folder_name],
execs.[project_name],
execs.[package_name],
execs.[reference_id],
execs.[reference_type],
execs.[environment_folder_name],
execs.[environment_name],
execs.[project_lsn],
execs.[executed_as_sid],
execs.[executed_as_name],
execs.[use32bitruntime],
opers.[operation_type],
opers.[created_time],
opers.[object_type],
opers.[object_id],
opers.[status],
opers.[start_time],
opers.[end_time],
opers.[caller_sid],
opers.[caller_name],
opers.[process_id],
opers.[stopped_by_sid],
opers.[stopped_by_name],
opers.[operation_guid] as [dump_id],
opers.[server_name],
opers.[machine_name],
ossysinfos.[total_physical_memory_kb],
ossysinfos.[available_physical_memory_kb],
ossysinfos.[total_page_file_kb],
ossysinfos.[available_page_file_kb],
ossysinfos.[cpu_count]
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 @ExecutionID=N’132′
SELECT     opmsg.[operation_message_id] as [event_message_id],
opmsg.[operation_id],
opmsg.[message_time],
opmsg.[message_type],
opmsg.[message_source_type],
opmsg.[message],
opmsg.[extended_info_id],
eventmsg.[package_name],
eventmsg.[event_name],
message_source_name =
CASE
WHEN (opmsg.message_source_type = 10) THEN ‘ISServerExec’
WHEN (opmsg.message_source_type = 20) THEN ‘Transact-SQL stored procedure’
ELSE eventmsg.message_source_name
END,
eventmsg.[message_source_id],
eventmsg.[subcomponent_name],
eventmsg.[package_path],
eventmsg.[execution_path],
eventmsg.[threadID],
eventmsg.[message_code]
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.

image

Table definition

Here I list the tables and its definitions copied from BOL.

Schema

Table name

Definition

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_permissions
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_message_context
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 folder_permissions
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 project_permissions
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.

Scripts

Code definition tables

Reza Rad shared a script that creates code conversion tables. I created and added a table for permsssion_type. You can download the script from SSISDB_CodeTables.

About these ads

One Response to “SSISDB ERD – A Journey to DB deployment automation”

  1. […] SSIS Castalog – SSISDB ERD […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

%d bloggers like this: