SSISDB2：SSIS工程的操作实例

SSISDB 系列随笔汇总：

• SSISDB1：使用SSISDB管理Package
• SSISDB2：SSIS工程的操作实例
• SSISDB3：Package的执行实例
• SSISDB4：当前正在运行的Package及其Executable
• SSISDB5：使用TSQL脚本执行Package
• SSISDB6：参数和环境变量

操作实例（Operation）是对SSIS Project所做的任何一次操作，例如，部署SSIS工程，执行Package等，都被抽象成一个操作。在执行每一次操作时，SSISDB都会记录操作的执行情况和消息。消息的记录是由事件触发时的，在触发事件时，SSIS引擎会记录事件产生的消息和事件的上下文，通过操作实例的消息，能够监控操作实例的运行情况，对已发生的错误进行故障排除。

在实际的项目开发过程中，通常使用SQL Server Agent创建Job来调用Package，每一个Job Step都会创建一个操作实例，分配一个操作实例的ID。操作实例分为用户操作和系统操作，共计10种类型：

• 1 ：Integration Services initialization
• 2 ：Retention window
• 3 ：MaxProjectVersion
• 101 ：deploy_project
• 106 ：restore_project
• 200 ：create_execution and start_execution
• 202 ：stop_operation
• 300 ：validate_project
• 301 ：validate_package
• 1000 ：configure_catalog

系统操作实例主要用于系统维护和版本控制，而用户操作实例主要是Package的执行。

一，维护操作

操作实例产生的消息可能很多，SSIS引擎会把操作实例产生的消息都记录到SSISDB数据库中，随着时间的增长，数据库记录的消息数据会持续增大，导致SSISDB数据库变得臃肿，占用的硬盘空间增加，查询性能变慢。

SSIS引擎自动创建一个用于管理SSISDB的Agent，叫做“SSIS Server Maintenance Job”，该Job每天运行一次，共有两个Job Step，分别用于移除操作的消息记录和工程版本（Project Version）。这个Job的两个Step通过调用SP来清理过时的数据和工程版本：

EXEC [internal].[cleanup_server_retention_window]
EXEC [internal].[cleanup_server_project_version]

如果操作类型（Operation Type）是 Retention Windows，表示SSIS引擎执行历史消息的清理操作，将保持窗口（Retention window）之外的操作记录及其消息删除；如果操作类型是MaxProjectVersion，表示SSIS引擎执行工程版本清理操作，维持一定数量的Project version，将老的的Project Version删除。

在清理过时的数据和版本时，用户可以通过SSMS（依次点击Integration ServicesSSISDBProperties）查看SSIS的属性，用户只需要修改配置属性，就能控制Agent在何时清理过时的数据，

用户可以通过视图：catalog.catalog_properties 查看，集成服务引擎的属性

• VERSION_CLEANUP_ENABLED：当该属性值是TRUE时，表示启用版本清理，SSISDB只存储指定数量（MAX_PROJECT_VERSIONS）的工程版本，过时的工程版本被删除；当该属性值是FALSE时，禁用版本删除，保存所有的的版本。
  
• MAX_PROJECT_VERSIONS：该属性值表示SSISDB最多为一个工程（Project）保存的版本数量。如果启用版本清理功能，那么大于该版本的工程版本将会被删除。
• OPERATION_CLEANUP_ENABLED：该属性值为TRUE时，表示启用操作消息清理，SSISDB只存储在特定时间窗口内的操作的历史记录和关于操作的消息，超过时间窗口的消息和历史记录被删除；如果该属性值是FALSE，那么SSISDB存储操作的所有历史记录和关于操作的所有消息，不会清理操作的任何信息。
• RETENTION_WINDOW：该属性设置保持操作消息的时间窗口，单位是天（day），如果设置为-1，表示时间窗口无限大；注意，如果不做操作消息的清理，那么设置OPERATION_CLEANUP_ENABLED为FALSE，用户可以禁用操作消息的清理。
  

二，从SSISDB中查询操作实例

在查询操作实例之前，首先创建辅助数据，使用以下的脚本创建，便于窗口类型ID对应的类型文本（Name）：

use SSISDB
go 

create schema helper
go

create table helper.OperationType
(
operation_type int not null,
operation_type_descr varchar(256) not null,
invoker varchar(256) not null
)
go

insert into helper.OperationType
(
operation_type,
operation_type_descr,
invoker
)
values
(1,  'Integration Services initialization','')
,(2, 'Retention window','SQL Agent job')
,(3, 'MaxProjectVersion','SQL Agent job') 
,(101, 'deploy_project','Stored procedure') 
,(106, 'restore_project','Stored procedure') 
,(200, 'create_execution and start_execution','Stored procedures') 
,(202, 'stop_operation','Stored procedure') 
,(300, 'validate_project','Stored procedure') 
,(301, 'validate_package','Stored procedure') 
,(1000, 'configure_catalog','Stored procedure') 
go

create table helper.MessageType
(
message_type int not null,
message_type_descr varchar(256) not null
)
go

insert into helper.MessageType
(
message_type,
message_type_descr
)
values 
(-1,'Unknown')
,(120,'Error')
,(110,'Warning')
,(70,'Information')
,(10,'Pre-validate')
,(20,'Post-validate')
,(30,'Pre-execute')
,(40,'Post-execute')
,(60,'Progress')
,(50,'StatusChange')
,(100,'QueryCancel')
,(130,'TaskFailed')
,(90,'Diagnostic')
,(200,'Custom')
,(140,'DiagnosticEx, Whenever an Execute Package task executes a child package, it logs this event. The event message consists of the parameter values passed to child packages.')
,(400,'NonDiagnostic')
,(80,'VariableValueChanged')
go
 
create table helper.MessageSourceType
(
message_source_type int not null,
message_source_descr varchar(256)
)
go

insert into helper.MessageSourceType
(
message_source_type,
message_source_descr
)
values 
(10,'Entry APIs, such as T-SQL and CLR Stored procedures')
,(20,'External process used to run package (ISServerExec.exe)')
,(30,'Package-level objects')
,(40,'Control Flow tasks')
,(50,'Control Flow containers')
,(60,'Data Flow task') 
go

create table helper.OperationStatus
(
operation_status int not null,
operation_status_descr varchar(256) not null
)
go

insert into helper.OperationStatus
(
operation_status,
operation_status_descr
)
values 
(1,'created')
,(2,'running')
,(3,'canceled')
,(4,'failed')
,(5,'pending')
,(6,'ended unexpectedly')
,(7,'succeeded')
,(8,'stopping')
,(9,'completed')
go

create table helper.ObjectType
(
object_type int not null,
object_type_descr varchar(256) not null
)
go

insert into helper.ObjectType
(
object_type,
object_type_descr
)
values 
(10,'folder')
,(20,'project')
,(30,'package')
,(40,'environment')
,(50,'instance of execution')
go

create table helper.ContextType 
(
context_type int not null,
context_type_name varchar(256) not null,
context_type_descr varchar(256) not null,
)
go

insert into helper.ContextType
(
context_type,
context_type_name,
context_type_descr
)
values 
(10,'Task','State of a task when an error occurred.')
,(20,'Pipeline','Error from a pipeline component: source, destination, or transformation component.')
,(30,'Sequence','State of a sequence.')
,(40,'For Loop','State of a For Loop')
,(50,'Foreach Loop','State of a Foreach Loop')
,(60,'Package','State of the package when an error occurred.')
,(70,'Variable','Variable value')
,(80,'Connection Manage','Properties of a connection manager.')
go

create table helper.ExecutionResult
(
execution_result int not null,
execution_result_descr varchar(256) not null
)
go 

insert into helper.ExecutionResult 
(
execution_result,
execution_result_descr
)
values 
(0,'Success')
,(1,'Failure')
,(2,'Completion')
,(3,'Cancelled')
go

1，查询操作实例

按照操作实例开始的时间倒序查询操作实例，对于系统创建的自动清理的操作实例，其object_type 是NULL

select top 11
    o.operation_id,
    opt.operation_type_descr　,
    o.created_time,
    o.object_id,
    obt.object_type_descr,
    o.object_name,
    ops.operation_status_descr,
    o.start_time,
    o.end_time
from catalog.operations o 
inner join helper.OperationType opt 
    on o.operation_type=opt.operation_type
inner join helper.OperationStatus ops
    on o.status=ops.operation_status
left join helper.ObjectType obt
    on o.object_type=obt.object_type
order by o.start_time desc 

返回的操作实例的信息，字段operation_status_descr是操作实例的状态，running表示正在执行，succeeded表示执行成功，failed表示执行失败等。

2，查看指定操作实例的消息

根据字段message_type，能够查询特定类型的消息，例如， 120表示错误消息（Error）；消息源是指产生消息的源头，最常见的消息源是：控制流任务（Control Flow Task，类型是40），控制流容器（Control Flow Container，类型是50），数据流组件（Data Flow Task，类型是60），通过查看操作实例的消息，能够监控到Package运行过程中的状态。

select om.operation_message_id,
    om.message_time,
    mt.message_type_descr,
    om.message_source_type,
    mst.message_source_descr,
    om.message
from catalog.operation_messages om 
inner join helper.MessageType mt 
    on om.message_type=mt.message_type
inner join helper.MessageSourceType mst
    on om.message_source_type=mst.message_source_type
where om.operation_id=23537
　　-- and mt.message_type=120  --error message
order by om.message_time desc

3，查看操作实例的Event Message

Event Message是关于Message的信息，可以认为和Message是一一对应的。

select em.event_message_id,
    em.package_name,
    em.package_path,
    em.subcomponent_name,
    em.event_name,
    mt.message_type_descr,
    mst.message_source_descr,
    --em.message_source_id,
    em.message_source_name,
    em.message,
    cast(em.message_time as DATETIME) as message_time,
    em.execution_path
from catalog.event_messages em with(nolock)
inner join helper.MessageType mt 
    on em.message_type=mt.message_type
inner join helper.MessageSourceType mst 
    on em.message_source_type=mst.message_source_type
where em.operation_id=23537
order by em.message_time desc

4，查看指定Event Message的Context

Event Message的上下文（Context）是指Package的组件（Task、Pipeline、Sequence、For Loop、Foreach Loop、Package、Variable、Connection Manage）等在事件发生时，它们的属性值是什么，事件消息的上下文主要用于故障排除。当错误事件发生时，可以查看变量（Variable）的值，便于快速查找到Package的出错原因。

select emc.context_id,
    emc.context_depth,
    emc.package_path,
    ct.context_type_name,
    ct.context_type_descr,
    emc.context_source_name,
    --emc.context_source_id,
    emc.property_name,
    emc.property_value
from catalog.event_message_context emc with(nolock)
inner join helper.ContextType ct 
    on emc.context_type=ct.context_type
where emc.event_message_id=6713840
order by emc.context_type 

5，用于故障排除的脚本

select 
    opt.operation_type_descr as Operation,
    obt.object_type_descr as object_type,
    o.object_name,
    ops.operation_status_descr as OperationStatus,
    mt.message_type_descr as message_type,
    mst.message_source_descr,
    om.message,
    om.message_time
from catalog.operations o
inner join helper.OperationType opt
    on o.operation_type=opt.operation_type
inner join helper.ObjectType obt 
    on o.object_type=obt.object_type
inner join helper.OperationStatus ops
    on o.status=ops.operation_status
inner join catalog.operation_messages om
    on o.operation_id=om.operation_id
inner join helper.MessageType mt 
    on om.message_type=mt.message_type
inner join helper.MessageSourceType mst 
    on om.message_source_type=mst.message_source_type
where o.operation_id =104627 
    and om.message_type in
    (
        120,--Error
        110,--Warning
        130--TaskFailed
    )
order by om.message_time desc

select
    opt.operation_type_descr as Operation,
    obt.object_type_descr as object_type,
    o.object_name,
    ops.operation_status_descr as OperationStatus,
    em.event_message_id,
    em.package_name,
    em.event_name,
    em.message_source_name,
    em.subcomponent_name,
    mt.message_type_descr as message_type,
    mst.message_source_descr as message_source_type,
    em.package_path,
    em.event_message_id,
    em.message_time,
    em.message
from catalog.operations o
inner join helper.OperationType opt 
    on o.operation_type=opt.operation_type
inner join helper.OperationStatus ops 
    on o.status=ops.operation_status
inner join helper.ObjectType obt 
    on o.object_type=obt.object_type
inner join catalog.event_messages em
    on o.operation_id=em.operation_id
inner join helper.MessageType mt 
    on em.message_type=mt.message_type
inner join helper.MessageSourceType mst 
    on em.message_source_type=mst.message_source_type
where o.operation_id =104627 
    and em.message_type in
    (
        120,    --Error
        110,    --Warning
        130     --TaskFailed;
    )
    --and em.package_name=N'PackageName.dtsx'
order by em.message_time desc


select emc.context_depth,
    emc.package_path,
    ct.context_type_name as context_type,
    emc.context_source_name,
    emc.property_name,
    emc.property_value
from catalog.event_message_context emc
inner join helper.ContextType ct 
    on emc.context_type=ct.context_type
where emc.event_message_id=23929777
and emc.context_type=70

附：创建SSISDB Catalog的辅助表

use SSISDB
go 

create schema helper
go

create table helper.OperationType
(
operation_type int not null,
operation_type_descr varchar(256) not null,
invoker varchar(256) not null
)
go

insert into helper.OperationType
(
operation_type,
operation_type_descr,
invoker
)
values
(1,  'Integration Services initialization','')
,(2, 'Retention window','SQL Agent job')
,(3, 'MaxProjectVersion','SQL Agent job') 
,(101, 'deploy_project','Stored procedure') 
,(106, 'restore_project','Stored procedure') 
,(200, 'create_execution and start_execution','Stored procedures') 
,(202, 'stop_operation','Stored procedure') 
,(300, 'validate_project','Stored procedure') 
,(301, 'validate_package','Stored procedure') 
,(1000, 'configure_catalog','Stored procedure') 
go

create table helper.MessageType
(
message_type int not null,
message_type_descr varchar(256) not null
)
go

insert into helper.MessageType
(
message_type,
message_type_descr
)
values 
(-1,'Unknown')
,(120,'Error')
,(110,'Warning')
,(70,'Information')
,(10,'Pre-validate')
,(20,'Post-validate')
,(30,'Pre-execute')
,(40,'Post-execute')
,(60,'Progress')
,(50,'StatusChange')
,(100,'QueryCancel')
,(130,'TaskFailed')
,(90,'Diagnostic')
,(200,'Custom')
,(140,'DiagnosticEx, Whenever an Execute Package task executes a child package, it logs this event. The event message consists of the parameter values passed to child packages.')
,(400,'NonDiagnostic')
,(80,'VariableValueChanged')
go
 
create table helper.MessageSourceType
(
message_source_type int not null,
message_source_descr varchar(256)
)
go

insert into helper.MessageSourceType
(
message_source_type,
message_source_descr
)
values 
(10,'Entry APIs, such as T-SQL and CLR Stored procedures')
,(20,'External process used to run package (ISServerExec.exe)')
,(30,'Package-level objects')
,(40,'Control Flow tasks')
,(50,'Control Flow containers')
,(60,'Data Flow task') 
go

create table helper.OperationStatus
(
operation_status int not null,
operation_status_descr varchar(256) not null
)
go

insert into helper.OperationStatus
(
operation_status,
operation_status_descr
)
values 
(1,'created')
,(2,'running')
,(3,'canceled')
,(4,'failed')
,(5,'pending')
,(6,'ended unexpectedly')
,(7,'succeeded')
,(8,'stopping')
,(9,'completed')
go

create table helper.ObjectType
(
object_type int not null,
object_type_descr varchar(256) not null
)
go

insert into helper.ObjectType
(
object_type,
object_type_descr
)
values 
(10,'folder')
,(20,'project')
,(30,'package')
,(40,'environment')
,(50,'instance of execution')
go

create table helper.ContextType 
(
context_type int not null,
context_type_name varchar(256) not null,
context_type_descr varchar(256) not null,
)
go

insert into helper.ContextType
(
context_type,
context_type_name,
context_type_descr
)
values 
(10,'Task','State of a task when an error occurred.')
,(20,'Pipeline','Error from a pipeline component: source, destination, or transformation component.')
,(30,'Sequence','State of a sequence.')
,(40,'For Loop','State of a For Loop')
,(50,'Foreach Loop','State of a Foreach Loop')
,(60,'Package','State of the package when an error occurred.')
,(70,'Variable','Variable value')
,(80,'Connection Manage','Properties of a connection manager.')
go

create table helper.ExecutionResult
(
execution_result int not null,
execution_result_descr varchar(256) not null
)
go 

insert into helper.ExecutionResult 
(
execution_result,
execution_result_descr
)
values 
(0,'Success')
,(1,'Failure')
,(2,'Completion')
,(3,'Cancelled')
go

参考文档：

catalog.operations (SSISDB Database) 

catalog.catalog_properties (SSISDB Database)

SSIS Catalog: Part 6 - Operations