SSISDB2：SSIS工程的操作实例-白红宇的个人博客

发布日期：2021-05-09 04:55:52 浏览次数：7 分类：博客文章

本文共 13540 字，大约阅读时间需要 45 分钟。

SSISDB 系列随笔汇总：

操作实例（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 Services\SSISDB\Properties）查看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 SSISDBgo create schema helpergocreate table helper.OperationType(operation_type int not null,operation_type_descr varchar(256) not null,invoker varchar(256) not null)goinsert 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') gocreate table helper.MessageType(message_type int not null,message_type_descr varchar(256) not null)goinsert 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))goinsert 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') gocreate table helper.OperationStatus(operation_status int not null,operation_status_descr varchar(256) not null)goinsert 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')gocreate table helper.ObjectType(object_type int not null,object_type_descr varchar(256) not null)goinsert into helper.ObjectType(object_type,object_type_descr)values (10,'folder'),(20,'project'),(30,'package'),(40,'environment'),(50,'instance of execution')gocreate table helper.ContextType (context_type int not null,context_type_name varchar(256) not null,context_type_descr varchar(256) not null,)goinsert 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.')gocreate 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

View Code

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_timefrom catalog.operations o inner join helper.OperationType opt     on o.operation_type=opt.operation_typeinner join helper.OperationStatus ops    on o.status=ops.operation_statusleft join helper.ObjectType obt    on o.object_type=obt.object_typeorder 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.messagefrom catalog.operation_messages om inner join helper.MessageType mt     on om.message_type=mt.message_typeinner join helper.MessageSourceType mst    on om.message_source_type=mst.message_source_typewhere om.operation_id=23537　　-- and mt.message_type=120  --error messageorder 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_pathfrom catalog.event_messages em with(nolock)inner join helper.MessageType mt     on em.message_type=mt.message_typeinner join helper.MessageSourceType mst     on em.message_source_type=mst.message_source_typewhere em.operation_id=23537order 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_valuefrom catalog.event_message_context emc with(nolock)inner join helper.ContextType ct     on emc.context_type=ct.context_typewhere emc.event_message_id=6713840order 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_timefrom catalog.operations oinner join helper.OperationType opt    on o.operation_type=opt.operation_typeinner join helper.ObjectType obt     on o.object_type=obt.object_typeinner join helper.OperationStatus ops    on o.status=ops.operation_statusinner join catalog.operation_messages om    on o.operation_id=om.operation_idinner join helper.MessageType mt     on om.message_type=mt.message_typeinner join helper.MessageSourceType mst     on om.message_source_type=mst.message_source_typewhere o.operation_id =104627     and om.message_type in    (        120,--Error        110,--Warning        130--TaskFailed    )order by om.message_time descselect    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.messagefrom catalog.operations oinner join helper.OperationType opt     on o.operation_type=opt.operation_typeinner join helper.OperationStatus ops     on o.status=ops.operation_statusinner join helper.ObjectType obt     on o.object_type=obt.object_typeinner join catalog.event_messages em    on o.operation_id=em.operation_idinner join helper.MessageType mt     on em.message_type=mt.message_typeinner join helper.MessageSourceType mst     on em.message_source_type=mst.message_source_typewhere 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 descselect emc.context_depth,    emc.package_path,    ct.context_type_name as context_type,    emc.context_source_name,    emc.property_name,    emc.property_valuefrom catalog.event_message_context emcinner join helper.ContextType ct     on emc.context_type=ct.context_typewhere emc.event_message_id=23929777and emc.context_type=70

View Code

附：创建SSISDB Catalog的辅助表

use SSISDBgo create schema helpergocreate table helper.OperationType(operation_type int not null,operation_type_descr varchar(256) not null,invoker varchar(256) not null)goinsert 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') gocreate table helper.MessageType(message_type int not null,message_type_descr varchar(256) not null)goinsert 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))goinsert 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') gocreate table helper.OperationStatus(operation_status int not null,operation_status_descr varchar(256) not null)goinsert 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')gocreate table helper.ObjectType(object_type int not null,object_type_descr varchar(256) not null)goinsert into helper.ObjectType(object_type,object_type_descr)values (10,'folder'),(20,'project'),(30,'package'),(40,'environment'),(50,'instance of execution')gocreate table helper.ContextType (context_type int not null,context_type_name varchar(256) not null,context_type_descr varchar(256) not null,)goinsert 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.')gocreate 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

View Code

参考文档：

上一篇：SSIS 容器

下一篇：R学习第十篇：包

发表评论

关于作者

喝酒易醉，品茶养心，人生如梦，品茶悟道，何以解忧？唯有杜康！

-- 愿君每日到此一游！

发表评论

最新留言

关于作者

推荐文章