这是我参与11月更文挑战的第19天，活动详情查看：2021最后一次更文挑战

翻译参考

本文主要参考翻译自 The Stairway to Integration Services 系列文章的 Advanced Logging - Level 12 of the Stairway to Integration Services，目的在于对 SSIS 有一个全面清晰的认识，所有内容在原文的基础上进行实操，由于版本差异、个人疑问等多种原因，未采用完全翻译的原则，同时也会对文中内容进行适当修改，希望最终可以更有利于学习和了解 SSIS，

感谢支持！

高级日志

在之前的介绍中我们配置了SSIS的内建日志。演示了简单和高级日志配置、导出导入日志配置，以及使用脚本任务(Script Tasks)和Dts.Events对象生成自定义日志消息。在SSIS学习使用四：控制流任务错误处理中，我们讨论了冒泡事件的行为、分享了操作事件冒泡的默认行为及介绍了父子模式。

本篇我们将结合学到的关于事件冒泡、日志和父子模式来创建一个自定义的SSIS包日志模型(模式)。

前提条件

禁用Precedence.dtsx事件处理

点击 Precedence.dtsx SSIS包控制流的任何空白处，按F4打开属性。修改 DisableEventHandlers 属性为True。

禁用内建日志(Disable Built-In Logging)

我们要做的第一件事是从SSIS包中移除存在的日志配置。

点击顶部的SSIS下拉菜单，然后点击"日志记录"(Logging...)。

当 配置SSIS日志(Configure SSIS Logs) 窗口显示后，点击 "删除"(Delete) 按钮，删除存在的 文本日志文件(text log file) 配置。

技术上讲，这将停止日志记录 Precedence.dtsx SSIS包。同时推荐清理一下SSIS包，因此建议在 "配置SSIS日志" 窗口中取消选中Precedence包，如下：

并建议删除日志文件连接管理器(log file connection manager)：

这样就清除了上一部分配置的内建日志。

推荐复习下第10部分高级事件行为中的事件冒泡...

准备父子SSIS设计模式日志记录(Preparing for Parent-Child SSIS Design Pattern Logging)

首先，我们需要一个数据库和表用于记录日志。让我们创建一个名为 SSISStairwayConfig 的数据库。

使用下面的脚本创建数据库

Use master
go
/* SSISStairwayConfig database */
If Not Exists(Select name
              From sys.databases
              Where name = 'SSISStairwayConfig')
 begin
  print 'Creating SSISStairwayConfig database'
  Create Database SSISStairwayConfig
  print 'SSISStairwayConfig database created'
 end
Else
 print 'SSISStairwayConfig database already exists.'
go

接下来，使用下面的脚本创建 lg 架构(schema) 和 SSISErrors表 用于日志记录。

Use SSISStairwayConfig
go
/* log schema */
If Not Exists(Select name
              From sys.schemas
              Where name = 'lg')
 begin
  print 'Creating lg schema'
  declare @sql varchar(100) = 'Create Schema lg'
  exec(@sql)
  print 'Lg schema created'
 end
Else
 print 'Lg schema already exists.'

/* lg.SSISErrors table */
If Not Exists(Select s.name + '.' + t.name
              From sys.tables t
              Join sys.schemas s
                On s.schema_id = t.schema_id
              Where s.name = 'lg'
                And t.name = 'SSISErrors')
 begin
  print 'Creating lg.SSISErrors table'
  Create Table lg.SSISErrors
  (
    ID int identity(1,1)  Constraint PK_SSISErrors Primary Key Clustered,
    ErrorDateTime datetime Not Null Constraint DF_logSSISErrors_ErrorDateTime Default(GetDate()),
    ErrorDescription varchar(max) Null,
    ErrorCode int Null,
    SourceName varchar(255) Null
  )
  print 'Lg.SSISErrors created'
 end
Else
 print 'Lg.SSISErrors table already exists.'

我们将防止来自SSIS包的error数据到SSISStairwayConfig数据库的lg.SSISErrors表。

将父子SSIS设计模式应用于日志记录(Applying the Parent-Child SSIS Design Pattern to Logging)

打开 Parent.dtsx SSIS包的 "事件处理程序"(Event Handlers) 标签页，导航到 "Parent" 可执行程序 和 "OnError"事件处理器。

拖拽一个"执行SQL任务"(Execute SQL Task)到OnError事件处理器界面，从"脚本任务"连接一个成功优先约束到新的"执行SQL任务"

打开"执行SQL任务"编辑器，修改 ConnectionType 属性为 ADO.NET。

点击 Connection 属性的值下拉列表，然后点击 "新建连接"(New connection)：

“新建连接”选项可以为我们做三件事。

首先，它选择正确的连接管理器类型：ADO.NET连接。

其次，它在SSDT编辑器底部的 "连接管理器"(Connection Managers) 选项卡中创建一个新的 ADO.NET连接管理器，

第三，新建连接选项打开 配置ADO.NET连接管理器编辑器(Configure ADO.NET Connection Manager editor)

点击"新建"按钮，配置一个新的数据连接。

在“服务器名”(Server Name)下拉框中，选择或输入之前创建SSISStairwayConfig数据库的 SQL Server 实例的服务器和实例名。从“选择或输入一个数据库名”(Select or enter a database name)的下拉列表中选择或输入SSISStairwayConfig。

单击“测试连接”按钮，以确保具有访问 SSISStairwayConfig 数据库的权限。如下为测试连接成功：

点击"确定"，关闭连接管理器编辑器，返回 "配置ADO.NET连接管理器" 窗口。

"数据连接"(Data connections)存储在工作站的Windows配置文件中。"数据连接"信息将会在工作站上保持可用，并在将来开发SSIS包中可访问。点击"确定"(OK)，关闭该窗口。

SSIS已将连接管理器命名为 “WIN-FR5GRQSCDPO.SSISStairwayConfig”，并将该名称放置在 “执行SQL任务” 的 “Connection” 属性中。 实际的连接管理器 “WIN-FR5GRQSCDPO.SSISStairwayConfig” 还可以在 “连接管理器” 窗口中找到。

下面，点击 "SQLStatement" 属性的值文本框的内部，然后点击"省略号"，打开 "输入SQL查询"(Enter SQL Query) 窗口。输入下面的语句

Insert Into lg.SSISErrors
(ErrorCode
,ErrorDescription
,SourceName)
Values
(@ErrorCode
,@ErrorDescription
,@SourceName)

输入SQL查询 的窗口如下：

点击"确定"，关闭该窗口。

创建的 Insert 语句包含三个参数：ErrorCode, ErrorDescription和SourceName。这些参数和之前在 Parent.dtsx SSIS包的 OnError事件处理程序 的 "脚本任务"(之前在SSIS学习使用四：控制流任务错误处理中创建) 读取到的SSIS变量相似。

点击 "执行SQL任务"编辑器 左侧列表中的 "参数映射"(Parameter Mapping)，打开参数映射页，在此处连接 SQL查询参数 和 OnError事件处理程序 变量。

点击 Add 按钮，点击 "变量名"(Variable Name)下拉框 修改变量名为 “System::ErrorCode”。修改 "参数名称"(Parameter Name) 为 "ErrorCode"。

System::ErrorCodeSSIS变量是一个 Int32(integer) 数据类型，它的值会代替 Insert 语句中的 @ErrorCode 参数。

"方向"(Direction)选项是：Input, Output 和 ReturnValue，数据类型(Data Type)列包含一个 ADO.Net 数据类型的列表。为什么是 ADO.Net 数据类型，因为在开始配置当前 "执行SQL任务"("常规页") 时我们选择的 Connection Type。如果 Connection Type 属性选择的是 OLEDB，此处的数据类型将会不同。参数大小(Parameter Size)不用修改。

ADO.Net 和 OLEDB 连接类型之前的另一个不同是：可以在SQL语句和 ADO.NET 下的"参数名称"列中使用参数的名字。当使用OLEDB时，在SQL语句中必须提供问号标记(question marks)，并使用数字引用参数名称(第一个问号为0，第二个问号为1，依此类推)。对于 Insert(和其他) 语句，ADO.Net的语法更清晰。

点击"添加"按钮，添加另一个参数：ErrorDescription。System::ErrorDescription SSIS变量是String数据类型，其值将代替 Insert 语句中的 @ErrorDescription 参数。

最后再添加SourceName参数的映射。System::SourceNameSSIS变量是String数据类型，其值将代替 Insert 语句中的@SourceName参数。

注意“参数映射”中的数据类型，如果类型不正确可能导致执行失败。

在SSDT的调试器中执行 Parent.dtsx 包，观察OnError事件处理程序。

询问 "Script Task 4" 成功时点击 "否"，Parent.dtsx SSIS包的 OnError事件处理程序 响应，之后 “执行SQL任务” 同样执行。

通过打开 SQL Server Management Studio (SSMS) 可以观察结果，执行语句如下：

Use SSISStairwayConfig
go
Select * From lg.SSISErrors;

结果显示如下：

弹出测验(Pop quiz)：在"父子模式"中，子包中需要多少行代码或对象来记录错误？ （答案：0）

我们还获得了 一致性 和 收集 的额外好处：每个错误消息都以相同的格式和位置记录。

We get the additional benefit of consistency and collection: Each error message is logged in the same format and location.

总结

在本文中，我们结合了先前了解的事件冒泡，日志记录 和 Parent-Child 模式的知识，创建自定义SSIS包日志记录模式。

我们使用 Parent.dtsx SSIS包的 OnError事件处理程序 和 Execute SQL Task 来捕获和存储有关子包（Precedence.dtsx）中引发的错误信息。无需在子包中添加其他逻辑即可完成此操作，并且实现了存储SSIS错误信息的一致格式和定位。