SSIS Catalog: Part 6 – Operations

In previous post of SSIS 2012 Catalog series, I talked about Logging and query log tables from Catalog. In this post you will read more about Operations tables about different tasks like deploy project with detailed information about each operation. I also provide some helper tables to help for writing more descriptive queries on operation tables.

Operations

This table contains information about each operation ran in
SSIS catalog. Each operation can be applied to an object and have a status as a
result. Operation_type and object_type and status columns contains ids that
pointing to helper tables that explained in next part.

Validations

information about validation of projects which contains folder name and package name

There is also a validate type and environment scope column which explained in next part (helper tables)

event_messages

this tables contains information about all messages passed during events like OnWarning, OnError

operation_os_sys_info

this table provides system information like cpu count, available physical memory and total physical memory at the time of operation

Operations table’s schema shown in image below

Helper tables

These tables are not exists in the SSISDB database. But you
can generate helper tables with scripts provided after each table.

First let’s create a schema for Helper tables:

USE [SSISDB]

GO

CREATE SCHEMA [Helper] AUTHORIZATION [dbo]

GO

After creating Helper schema, let’s talk about each table.

ObjectType

This tables have id and name of each object type like package, project …

used in many tables to demonstrate object type, like operations, executions.

OperationStatus

this tables shows result of an operation contains id and description of each result, like succeeded or failed…

used as status of many tables, for example operations 

OperationType

list of each operation type with its id, like deploy a project ….

used in executions, execution_info, validations and operations 

MessageType

list of messages passed during operations, like error, warning …

used in event_messages and operation_messages 

MessageSourceType

list of different sources where messages comes from, like control flow tasks …

used in event_messages and operation_messages 

ContextType

list of contexts and their id, like foreach loop

used in internal.event_message_context table or catalog.event_message_context view

ValidateType

list of validate types which are these two types:

used in validations table

EnvironmentScope

Indicates the environment
references that are considered by the validation.

used in validations table.

This is list of all helper tables under SSISDB database;

How to use Helper tables

This is an example of records exists in operation tables which queried without helper tables:

select * from internal.operations

or 

select * from catalog.operations 

Here you can see an example of enhanced queries with helper tables:

select 

operation_id,

helper.OperationType.Description as Operation_type,

created_time,

helper.ObjectType.Description as object_type,

object_id,

helper.OperationStatus.Description as status,

start_time,

end_time 

from internal.operations

inner join helper.OperationType

on operation_type=helper.OperationType.Value

inner join helper.ObjectType

on object_type=helper.ObjectType.Value

inner join helper.OperationStatus 

on status=helper.OperationStatus.Value

As you see using helper tables can produce more descriptive queries from operation tables.

Scripts to generate Helper schema and tables:

(You can download the script file from here

USE [SSISDB]

GO

/****** Object:  Schema [Helper]    Script Date: 8/21/2012 12:25:25 AM ******/

CREATE SCHEMA [Helper]

GO

/****** Object:  Table [Helper].[ContextType]    Script Date: 8/21/2012 12:25:25 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [Helper].[ContextType](

[Value] [smallint] NOT NULL,

[Description] [nvarchar](100) NULL,

 CONSTRAINT [PK_ContextType] PRIMARY KEY CLUSTERED 

(

[Value] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

/****** Object:  Table [Helper].[EnvirnmentScope]    Script Date: 8/21/2012 12:25:25 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [Helper].[EnvirnmentScope](

[Value] [char](1) NOT NULL,

[Description] [nvarchar](100) NULL,

 CONSTRAINT [PK_EnvirnmentScope] PRIMARY KEY CLUSTERED 

(

[Value] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

/****** Object:  Table [Helper].[MessageSourceType]    Script Date: 8/21/2012 12:25:25 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [Helper].[MessageSourceType](

[Value] [smallint] NOT NULL,

[Description] [nvarchar](100) NULL,

 CONSTRAINT [PK_MessageSourceType] PRIMARY KEY CLUSTERED 

(

[Value] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

/****** Object:  Table [Helper].[MessageType]    Script Date: 8/21/2012 12:25:25 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [Helper].[MessageType](

[Value] [smallint] NOT NULL,

[Description] [nvarchar](100) NULL,

 CONSTRAINT [PK_MessageType] PRIMARY KEY CLUSTERED 

(

[Value] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

/****** Object:  Table [Helper].[ObjectType]    Script Date: 8/21/2012 12:25:25 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [Helper].[ObjectType](

[Value] [smallint] NOT NULL,

[Description] [nvarchar](100) NULL,

 CONSTRAINT [PK_ObjectType] PRIMARY KEY CLUSTERED 

(

[Value] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

/****** Object:  Table [Helper].[OperationStatus]    Script Date: 8/21/2012 12:25:25 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [Helper].[OperationStatus](

[Value] [int] NOT NULL,

[Description] [nvarchar](100) NULL,

 CONSTRAINT [PK_OperationStatus] PRIMARY KEY CLUSTERED 

(

[Value] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

/****** Object:  Table [Helper].[OperationType]    Script Date: 8/21/2012 12:25:25 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [Helper].[OperationType](

[Value] [smallint] NOT NULL,

[Description] [nvarchar](100) NULL,

 CONSTRAINT [PK_OperationType] PRIMARY KEY CLUSTERED 

(

[Value] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

/****** Object:  Table [Helper].[ValidateType]    Script Date: 8/21/2012 12:25:25 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [Helper].[ValidateType](

[Value] [char](1) NOT NULL,

[Description] [nvarchar](100) NULL,

 CONSTRAINT [PK_ValidateType] PRIMARY KEY CLUSTERED 

(

[Value] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

INSERT [Helper].[ContextType] ([Value], [Description]) VALUES (10, N’Task’)

INSERT [Helper].[ContextType] ([Value], [Description]) VALUES (20, N’Pipeline’)

INSERT [Helper].[ContextType] ([Value], [Description]) VALUES (30, N’Sequence’)

INSERT [Helper].[ContextType] ([Value], [Description]) VALUES (40, N’For Loop’)

INSERT [Helper].[ContextType] ([Value], [Description]) VALUES (50, N’Foreach Loop’)

INSERT [Helper].[ContextType] ([Value], [Description]) VALUES (60, N’Package’)

INSERT [Helper].[ContextType] ([Value], [Description]) VALUES (70, N’Variable’)

INSERT [Helper].[ContextType] ([Value], [Description]) VALUES (80, N’Connection manager’)

INSERT [Helper].[EnvirnmentScope] ([Value], [Description]) VALUES (N’A’, N’All environment references associated with the project’)

INSERT [Helper].[EnvirnmentScope] ([Value], [Description]) VALUES (N’D’, N’No environment’)

INSERT [Helper].[EnvirnmentScope] ([Value], [Description]) VALUES (N’S’, N’Single environment’)

INSERT [Helper].[MessageSourceType] ([Value], [Description]) VALUES (10, N’Entry APIs, such as T-SQL and CLR Stored procedures’)

INSERT [Helper].[MessageSourceType] ([Value], [Description]) VALUES (20, N’External process used to run package (ISServerExec.exe)’)

INSERT [Helper].[MessageSourceType] ([Value], [Description]) VALUES (30, N’Package-level objects’)

INSERT [Helper].[MessageSourceType] ([Value], [Description]) VALUES (40, N’Control Flow tasks’)

INSERT [Helper].[MessageSourceType] ([Value], [Description]) VALUES (50, N’Control Flow containers’)

INSERT [Helper].[MessageSourceType] ([Value], [Description]) VALUES (60, N’Data Flow task’)

INSERT [Helper].[MessageType] ([Value], [Description]) VALUES (-1, N’Unknown’)

INSERT [Helper].[MessageType] ([Value], [Description]) VALUES (10, N’Pre-validate’)

INSERT [Helper].[MessageType] ([Value], [Description]) VALUES (20, N’Post-validate’)

INSERT [Helper].[MessageType] ([Value], [Description]) VALUES (30, N’Pre-execute’)

INSERT [Helper].[MessageType] ([Value], [Description]) VALUES (40, N’Post-execute’)

INSERT [Helper].[MessageType] ([Value], [Description]) VALUES (50, N’StatusChange’)

INSERT [Helper].[MessageType] ([Value], [Description]) VALUES (60, N’Progress’)

INSERT [Helper].[MessageType] ([Value], [Description]) VALUES (70, N’Information’)

INSERT [Helper].[MessageType] ([Value], [Description]) VALUES (80, N’VariableValueChanged’)

INSERT [Helper].[MessageType] ([Value], [Description]) VALUES (90, N’Diagnostic’)

INSERT [Helper].[MessageType] ([Value], [Description]) VALUES (100, N’QueryCancel’)

INSERT [Helper].[MessageType] ([Value], [Description]) VALUES (110, N’Warning’)

INSERT [Helper].[MessageType] ([Value], [Description]) VALUES (120, N’Error’)

INSERT [Helper].[MessageType] ([Value], [Description]) VALUES (130, N’TaskFailed’)

INSERT [Helper].[MessageType] ([Value], [Description]) VALUES (140, N’DiagnosticEx’)

INSERT [Helper].[MessageType] ([Value], [Description]) VALUES (200, N’Custom’)

INSERT [Helper].[MessageType] ([Value], [Description]) VALUES (400, N’NonDiagnostic’)

INSERT [Helper].[ObjectType] ([Value], [Description]) VALUES (10, N’Folder’)

INSERT [Helper].[ObjectType] ([Value], [Description]) VALUES (20, N’Projet’)

INSERT [Helper].[ObjectType] ([Value], [Description]) VALUES (30, N’Package’)

INSERT [Helper].[ObjectType] ([Value], [Description]) VALUES (40, N’Environment’)

INSERT [Helper].[ObjectType] ([Value], [Description]) VALUES (50, N’Instance of Execution’)

INSERT [Helper].[OperationStatus] ([Value], [Description]) VALUES (1, N’Created’)

INSERT [Helper].[OperationStatus] ([Value], [Description]) VALUES (2, N’Running’)

INSERT [Helper].[OperationStatus] ([Value], [Description]) VALUES (3, N’Canceled’)

INSERT [Helper].[OperationStatus] ([Value], [Description]) VALUES (4, N’Failed’)

INSERT [Helper].[OperationStatus] ([Value], [Description]) VALUES (5, N’Pending’)

INSERT [Helper].[OperationStatus] ([Value], [Description]) VALUES (6, N’Ended Unexpectedly’)

INSERT [Helper].[OperationStatus] ([Value], [Description]) VALUES (7, N’Succeeded’)

INSERT [Helper].[OperationStatus] ([Value], [Description]) VALUES (8, N’Stopping’)

INSERT [Helper].[OperationStatus] ([Value], [Description]) VALUES (9, N’Completed’)

INSERT [Helper].[OperationType] ([Value], [Description]) VALUES (1, N’Integration Services initialization’)

INSERT [Helper].[OperationType] ([Value], [Description]) VALUES (2, N’Retention window (SQL Agent job)’)

INSERT [Helper].[OperationType] ([Value], [Description]) VALUES (3, N’MaxProjectVersion (SQL Agent job)’)

INSERT [Helper].[OperationType] ([Value], [Description]) VALUES (101, N’deploy_project (Stored procedure)’)

INSERT [Helper].[OperationType] ([Value], [Description]) VALUES (106, N’restore_project (Stored procedure)’)

INSERT [Helper].[OperationType] ([Value], [Description]) VALUES (200, N’create_execution and start_execution (Stored procedures)’)

INSERT [Helper].[OperationType] ([Value], [Description]) VALUES (202, N’stop_operation (Stored procedure)’)

INSERT [Helper].[OperationType] ([Value], [Description]) VALUES (300, N’validate_project (Stored procedure)’)

INSERT [Helper].[OperationType] ([Value], [Description]) VALUES (301, N’validate_package (Stored procedure)’)

INSERT [Helper].[OperationType] ([Value], [Description]) VALUES (1000, N’configure_catalog (Stored procedure)’)

INSERT [Helper].[ValidateType] ([Value], [Description]) VALUES (N’D’, N’Dependency validation’)

INSERT [Helper].[ValidateType] ([Value], [Description]) VALUES (N’F’, N’Full Validation’)

Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.

Leave a Reply