微软BI 之SSIS 系列 - ErrorOutput 错误输出与处理

最新推荐文章于 2020-07-02 20:04:48 发布

lotusz

最新推荐文章于 2020-07-02 20:04:48 发布

阅读量4.4k

点赞数

分类专栏： Microsoft Bi 文章标签： BI ssis null

Microsoft Bi 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

在实际项目中，特别是从某数据源抽取数据到目标表的时候，我相信大家经常碰到的几种情况有：

1. 加载数据的时候在数据源端控件出现错误。

2. 数据源端的错误解决了，但是在数据目标控件位置又出现了错误。

3. 通过增加 Data Conversion 进行数据类型转换可能解决了错误使得整个流程成功了，但是为什么？

数据源端出现的错误。

目标端出现的错误。

可能存在的需求：

1. 遇到错误就忽略

2. 需要将错误数据导出到错误表以便查看

除了上述可能存在的需要解决的问题和需求，还有以下几个问题:

上图中 Error Output 到底应该如何使用？ Error 和 Truncation 分别对应的是什么验证？包括 Fail component , Ignore failure, Redirect row 应该如何使用？如果单个单个说，可能能解释的清楚，但是如果把上述这些内容全部揉为一团来解释各自彼此之间的关系与影响，相信是会觉得一下子很难解释清楚的。

所以本文特别就这些问题在这里总结一下，为了演示的更全面，这里选用了文件源和目标表搭配起来讲解，因为可以观察到两种不同的 Error Output。

认识 Error Output

在数据流中，我们通常能看到类似于这样带有两个向下箭头的组件。这种组件明确的告诉了我们它有一个正常的输出，同时还有错误数据的输出功能。

以这个 Flat File Source 组件为例，注意到它的 Error Output 选项下有这么几个列是需要理解和注意的 -

Column - Flat File Source 组件中的列名称。
Error - 对于每一个具体的 Column 列在转换时出现错误处理的方式，默认是 Fail component - 终止控件的运行，程序报错。
Truncation - 对于每一个具体的 Column 列在转换时出现因字符串过长而发生截断时的处理方式，这里也是 Fail component。

最下方还可供选择的有：

Ignore failure - 忽略错误 (但是一定要注意忽略错误之后，这个错误是如何被处理的！后面会提到...)

Redirect row - 转向，即错误内容将从 Error Output 分支输出。

但是在源中包括目标表中不同的设置也会影响这种错误处理的效果，下面来通过一张目标表配合起来演示这些不同的配置以及错误处理的效果。

通过案例理解 Error Output 中的 Error 与 Truncation 以及 Fail Component 与 Ignore Failure

IF OBJECT_ID(‘dbo.T007_STAGING_EMPLOYEE‘) IS NOT NULL
DROP TABLE dbo.T007_STAGING_EMPLOYEE
GO


CREATE TABLE [dbo].[T007_STAGING_EMPLOYEE](
 [ID] [int] IDENTITY(1,1) NOT NULL,
 [FirstName] [varchar](50) NULL,
 [LastName] [varchar](50) NULL,
 [MiddleName] [varchar](50) NULL,
 [NameStyle] [varchar](50) NULL,
 [Title] [varchar](50) NULL,
 [HireDate] [datetime] NULL,
 [BirthDate] [datetime] NULL,
 [EmailAddress] [varchar](50) NULL,
 [Phone] [varchar](50) NULL,
 [MaritalStatus] [varchar](50) NULL,
 [EmergencyContactName] [varchar](50) NULL,
 [EmergencyContactPhone] [varchar](50) NULL,
 [SalariedFlag] [varchar](50) NULL,
 [Gender] [varchar](50) NULL,
 [PayFrequency] [varchar](50) NULL,
 [BaseRate] [varchar](50) NULL,
 [VacationHours] [varchar](50) NULL,
 [SickLeaveHours] [varchar](50) NULL,
 [CurrentFlag] [varchar](50) NULL,
 [SalesPersonFlag] [varchar](50) NULL,
 [Department] [varchar](50) NULL
)

注意到我们的 HireDate 和 BirthDate 都是使用的 Datetime 类型，对于有些需求，初次从平面文件中抽取数据的时候往往都保留原始的文本类型。而有些需求，是需要在抽取之后就进行类型转换的，这里演示的是第二种需求。

测试文件中第1条和第2条数据会出现错误，一个是 HireDate 时间超长了，并且也无法转换为时间，第二个是 BirthDate 时间转换时一定会发生错误。我们要去理解的就是，这些错误到底会发生在哪个阶段？如何捕获和处理？这一点非常重要，因为在实际项目中对于这种错误数据OLE的处理要求各不相同，因此需要对这些概念和细节的设计非常熟悉。

同时，在文件连接管理器中的数据类型的设置也非常重要，它的改变也会影响错误处理的效果。

案例一 - 演示 Flat File Source 出错

Flat File Connection Manager

HireDate - 类型 string , 长度10
BirthDate - 类型 string , 长度10

Flat File Source

HireDate - Error - Fail Component
HireDate - Truncate - Fail Component
BirthDate - Error - Fail Component
BirthDate - Truncate - Fail Component

OLE DB Destination - Fail Component

将 HireDate 和 BirthDate 的输出长度改成 10，数据类型仍然保留为字符串类型。

执行包，出错的地方一定位于 FF_SRC_EMPLOYEE，因为 12000-07-31 超出了文件连接管理器中 HireDate 定义的10个长度。

报错信息和设想完全是一样的 -

[FF_SRC_EMPLOYEE [2]] Error: Data conversion failed. The data conversion for column "HireDate" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

案例二 - 演示 OLE DB Destination 出错，忽略 Truncate 错误

Flat File Connection Manager

HireDate - 类型 string , 长度10
BirthDate - 类型 string , 长度10

Flat File Source

HireDate - Error - Fail Component
HireDate - Truncate - Ignore Failure
BirthDate - Error - Fail Component
BirthDate - Truncate - Fail Component

OLE DB Destination - Fail Component

执行包，可以看到出错的位置出现在 OLE_DST_EMPLOYEE。原因很简单：对于 HireDate = 12000-07-31 这条数据来说，虽然它的长度超过 10 会发生截断，但是由于在 Truncate 处对 HireDate 设置了 Ignore failure，因此将按照10个位置截断并忽略这个错误。对于 BirthDate = 1981-06-33 这条数据，虽然它的长度满足文件连接管理器中 BirthDate 的定义，所以可以通过 Flat File Source 往下传递数据。但是对于 OLE_DST_EMPLOYEE 中的 T007_STAGING_EMPLOYEE 这张表来说，1981-06-33 是无法转换的，因此会出现错误。

错误信息 -

[OLE_DST_EMPLOYEE [95]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Invalid character value for cast specification".

[OLE_DST_EMPLOYEE [95]] Error: There was an error with OLE_DST_EMPLOYEE.Inputs[OLE DB Destination Input].Columns[HireDate] on OLE_DST_EMPLOYEE.Inputs[OLE DB Destination Input]. The column status returned was: "The value could not be converted because of a potential loss of data.".

此图展示 HireDate 被截断向下的效果 - 12000-07-3, 所以这条数据也是无法存入表中的。

案例三和案例二设置几乎一样，只是改变 OLE_DST_EMPLOYEE 的 Error Output 设置 - Ignore failure

结果自然是忽略这两条错误数据，其它正确的数据全部存入到表中。

虽然从 FF_SRC_EMPLOYEE 到 OLE_DST_EMPLOYEE 的传递过程中是 290 行数据，但是最终在表中只有 288 条数据。

案例四 - Flat File Source 源端错误处理

Flat File Connection Manager

HireDate - 类型 Date
BirthDate - 类型 Date

Flat File Source

HireDate - Error - Fail Component
HireDate - Truncate - Fail Component
BirthDate - Error - Fail Component
BirthDate - Truncate - Fail Component

OLE DB Destination - Fail Component

保存并运行包，结果是必定失败的。这两条错误的日期是无法在平面连接管理器中完成数据转换的，那么 Flat File Source 控件也就无法向下去传递数据给 OLE_DST_EMPLOYEE。

[FF_SRC_EMPLOYEE [2]] Error: Data conversion failed. The data conversion for column "HireDate" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

[FF_SRC_EMPLOYEE [2]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "FF_SRC_EMPLOYEE.Outputs[Flat File Source Output].Columns[HireDate]" failed because error code 0xC0209084 occurred, and the error row disposition on "FF_SRC_EMPLOYEE.Outputs[Flat File Source Output].Columns[HireDate]" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.

案例五 - Flat File Source 源端错误处理 - 忽略错误值转变为 NULL

Flat File Connection Manager

HireDate - 类型 Date
BirthDate - 类型 Date

Flat File Source

HireDate - Error - Ignore Failure
HireDate - Truncate - Ignore Failure
BirthDate - Error - Ignore Failure
BirthDate - Truncate - Ignore Failure

OLE DB Destination - Fail Component

可以看到在转换过程中，由于这两条数据在转换中发生错误，因此被忽略，同时被忽略的错误列以 NULL 的形式往下传递。这是一个非常重要的细节，在案例二 Truncate 的时候发生错误，我们选择了忽略，但是并非是 NULL 而是截断之后的字符串。所以 Error 和 Truncate 对 Ignore Failure 的处理是不一样的。

查看数据表看看最终的效果，NULL 值被插入到目标表中。

到此为止，上面的几个案例是为了让大家能够清楚的知道在源端，目标端通过 Error Output 中 Fail Component 和 Ignore Failure 两种设置数据流的表现情况。因为我们在实际项目中经常碰到这类错误，最常见的情形就是一开始的时候源端报错了，然后解决了源端的问题，结果目标端又出现了错误。第二则是，分不清到底是应该在目标连接管理器连接平面文件的时候就设置数据类型，还是中间通过 Data Conversion 转换再到目标端？第三就是，对于这种文件类型的数据转换，到底需要不需要 Data Conversion 来转换？相信理解了上述几个案例之后，这些问题出现的原因应该是能够弄清楚了。

错误输出的捕获

我们还有一个点没有讲解，那就是 Error Output 中的重定向 - Redirect Row。但是这里有一点是特别值得注意的就是，对于文件源来说，它的 Error Output 重定向输出的是什么？对于 OLE DB Destination 来说，它有 Error Output 吗？它的重定向 Redirect Row 是指什么？

以下也通过两个案例来讲解和描述 -

案例六 - Flat File Source 的 Error Output 重定向

确保平面文件连接管理器中的两个时间都是 Date 类型的，这样在加载平面文件的时候，由于那两条记录含有错误数据因此会发生失败。

在 Flat File Source 中 Error 处按下Shift 选中第一行和最后一行，然后选中 Redirect Row - Apply 一下。

同样的，Truncation 这里也这么来一下。当然，实际项目中可以取决于实际需求，比如可以截断所有列，或者允许截断部分列，那么都可以灵活的调整。

这些操作完成之后，这里一定有一个警告。因为选用了 Redirect Row，因此 Error Output 会往下游输出错误信息，需要有一个目标表/文件来接受。

拖放一个 OLE DB Destination 控件，这里我们使用表来接受错误信息。

编辑 OLE DB Destination，直接使用 Error Output 的结构创建一张新的表 - T007_STAGING_EMPLOYEE_ERROR_SOURCE。

其实从 Mapping 关系中就可以看出来，来自于 FF_SRC_EMPLOYEE 的 Error Output 向下输出的列即:

Flat File Source Error Output Column
ErrorCode
ErrorColumn

保存并运行，可以看到有 288 行正确的记录流向左边，有2条错误的记录走了 Error Output。

查看 T007_STAGING_EMPLOYEE_ERROR_SOURCE 的内容, 可以看到这两条错误数据的输出信息。

当然，在这里我们肯定有一个疑问，或者说进一步的想法 - 能够知道是哪一列出现的错误，具体的错误信息是什么？

ErrorCode 是很容易解析的，麻烦的是 Error Column。对于 2008 R2 来说，获取Error Column还算比较容易实现，直接解析 Package XML 文件。但是对于 2012 版本来说，包的 XML 结构已经全部变了，解析起来会比较困难一些，这一部分的内容会单独放在另一篇文章中讲解。

这里可以做什么？通常情况下，对于某些日志信息，大量的文件内容作为数据源的处理过程中，有部分记录发生错误是非常正常的。比如说1W条记录有几条出现解析错误，或者格式错误是非常非常正常的，因此我们把它以这种方式输出就完全可以达到统计错误记录的目的。

那么还存在一种情况，那就是用户希望能够手工的检查这些错误数据，希望可以在表中看到这些错误数据以便人工来审核与检查。这种情况下，就需要改变原有的设计了。

案例七 - OLE DB Destination 的 Error Output 重定向

如果需要在 OLE DB Destination 捕获这种错误格式的数据，这时需要放开 Flat File Source 的 Error Output。

平面文件链接管理器中的列都使用文件关联时的默认类型和长度，最大可能的保证所有的数据能够通过 Flat File Source。例外情况，今天来的文件格式都是没有问题的，通过了目前的设计，但是第二次来的格式就违背了已有的格式。在这种情况下，是需要检查到底哪一种文件格式是标准的，需要和上游输出者或客户进行沟通。

同时，Flat File Source 中都应该保持为默认的 Fail Component。

编辑 OLE DB Destination 设置 Error 的处理方式为 Redirect row。

添加一个新的 OLE DB Destination 作为 Error Output 的输出目标地。

编辑 OLE_DST_EMPLOYEE_ERROR 并借助默认配置创建表，这里我添加了一列 Execution ID 目的是区分不同的包执行的错误输出。同时要注意到，这时列的类型都保持了文件源中默认的类型，不再做类型定义都是字符串。

添加一个 Derived Column - Execution ID。

保存并执行包两次，都可以看到这样的一个效果，执行成功。

查看错误表记录，不仅可以看到不同的执行，而且可以看到错误的数据，这个数据表的结构和目标表的结构是一致的，只多了 Execution ID 这一列。

像这种错误输出表，用户就可以自行手工检查错误数据了。当然，这种设计一定要和包的日志设计搭配起来，让用户可以很容易的跟踪到具体是哪一天，哪一次的错误输出。再扩展延伸就可以做很多报表，专门用来统计这种错误输出记录的条数，包括用户可以很容易的通过报表来查阅这种错误数据等等。关于这些内容，就不再这里扩展讲解了。

更多 BI 文章请参看 BI 系列随笔列表 (SSIS, SSRS, SSAS, MDX, SQL Server)