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