数据中的错误处理Error Handling in Data

适用于: SQL Server 是 Azure 数据工厂中的 SSIS Integration Runtime Azure Synapse Analytics (SQL DW)APPLIES TO: yesSQL Server yesSSIS Integration Runtime in Azure Data Factory yesAzure Synapse Analytics (SQL DW)

数据流组件将转换应用到列数据、从源提取数据或将数据加载到目标中时,可能会发生错误。When a data flow component applies a transformation to column data, extracts data from sources, or loads data into destinations, errors can occur. 错误常因意外数据值而发生。Errors frequently occur because of unexpected data values. 例如,如果列包含字符串而不是数字,数据转换将失败;在数据库列中执行插入操作时,如果数据是日期而列的数据类型为数值,此操作将失败;如果因列值为零而导致数学运算无效,表达式将无法计算。For example, a data conversion fails because a column contains a string instead of a number, an insertion into a database column fails because the data is a date and the column has a numeric data type, or an expression fails to evaluate because a column value is zero, resulting in a mathematical operation that is not valid.

错误通常属于下列类别之一:Errors typically fall into one the following categories:

  • 数据转换错误,其在转换导致重要数字丢失、非重要数字丢失和字符串截断时发生。Data conversion errors, which occur if a conversion results in loss of significant digits, the loss of insignificant digits, and the truncation of strings. 如果不支持请求的转换,也会发生数据转换错误。Data conversion errors also occur if the requested conversion is not supported.

  • 表达式计算错误,其在运行时计算的表达式执行无效运算,或因数据值丢失或错误而出现语法错误时发生。Expression evaluation errors, which occur if expressions that are evaluated at run time perform invalid operations or become syntactically incorrect because of missing or incorrect data values.

  • 查找错误,其在查找操作在查找表中找不到匹配时发生。Lookup errors, which occur if a lookup operation fails to locate a match in the lookup table.

有关 Integration Services 的错误、警告和其他消息的列表,请参阅 Integration Services Error and Message ReferenceFor a list of Integration Services errors, warnings, and other messages, see Integration Services Error and Message Reference.

使用错误输出捕获行级错误Use error outputs to capture row-level errors

许多数据流组件支持错误输出,这使得您可以控制组件处理传入和传出数据中行级错误的方式。Many data flow components support error outputs, which let you control how the component handles row-level errors in both incoming and outgoing data. 通过设置输入或输出中各个列的选项,可以指定发生截断或错误时组件的行为。You specify how the component behaves when truncation or an error occurs by setting options on individual columns in the input or output. 例如,可以指定组件应在客户名称数据被截断时失败,但忽略另一包含不太重要数据的列上的错误。For example, you can specify that the component should fail if customer name data is truncated, but ignore errors on another column that contains less important data.

错误输出可以连接到另一个转换的输入,或者加载到非错误输出以外的目标。The error output can be connected to the input of another transformation or loaded into a different destination than the non-error output. 例如,错误输出可以连接到为空白列提供字符串的派生列转换。For example, the error output can be a connected to a Derived Column transformation that provides a string for a column that is blank.

下列关系图显示包含错误输出的简单数据流。The following diagram shows a simple data flow including an error output.

包含错误输出的数据流Data flow with error output

有关详细信息,请参阅 数据流Integration Services 路径For more information, see Data Flow and Integration Services Paths.

“配置错误输出”对话框Configure Error Output dialog box

可以使用 “配置错误输出” 对话框,为支持错误输出的数据流转换配置错误处理选项。Use the Configure Error Output dialog box to configure error handling options for data flow transformations that support an error output.

若要了解有关使用错误输出的详细信息,请参阅 数据中的错误处理To learn more about working with error outputs, see Error Handling in Data.

选项Options

输入或输出Input or Output
查看输出的名称。View the name of the output.

Column
查看转换编辑器对话框中所选的输出列。View the output columns that you selected in the transformation editor dialog box.

错误Error
如果适用,指定发生错误时应执行的操作:忽略失败、重定向行或使组件失败。If applicable, specify what should happen when an error occurs: ignore the failure, redirect the row, or fail the component.

相关主题: 数据中的错误处理Related Topics: Error Handling in Data

截断Truncation
如果适用,指定发生截断时应执行的操作:忽略失败、重定向行或使组件失败。If applicable, specify what should happen when a truncation occurs: ignore the failure, redirect the row, or fail the component.

相关主题: 数据中的错误处理Related Topics: Error Handling in Data

说明Description
查看操作的说明。View the description of the operation.

将此值设置到选定的单元格Set this value to selected cells
指定发生错误或截断时应对所有选定单元格执行的操作:忽略失败、重定向行或使组件失败。Specify what should happen to all the selected cells when an error or truncation occurs: ignore the failure, redirect the row, or fail the component.

应用Apply
将错误处理选项应用到选定的单元格。Apply the error handling option to the selected cells.

错误为失败或截断Errors are either failures or truncations

错误属于两个类别之一:错误或截断。Errors fall into one of two categories: errors or truncations.

“错误”Errors. 错误指示确定的失败,并且生成 NULL 结果。An error indicates an unequivocal failure, and generates a NULL result. 此类错误可以包括数据转换错误或表达式计算错误。Such errors can include data conversion errors or expression evaluation errors. 例如,尝试将包含字母字符的字符串转换为数字将导致错误。For example, an attempt to convert a string that contains alphabetical characters to a number causes an error. 数据转换、表达式计算和对变量、属性和数据列的表达式结果分配可能会由于非法转换和不兼容的数据类型而失败。Data conversions, expression evaluations, and assignments of expression results to variables, properties, and data columns may fail because of illegal casts and incompatible data types. 有关详细信息,请参阅转换 (SSIS 表达式)表达式中的 Integration Services 数据类型Integration Services 数据类型For more information see, Cast (SSIS Expression), Integration Services Data Types in Expressions, and Integration Services Data Types.

截断Truncations. 截断的严重程度小于错误。A truncation is less serious than an error. 截断生成的结果可能是有用的甚至是所希望的。A truncation generates results that might be usable or even desirable. 您可以将截断视为错误或可接受的情况。You can elect to treat truncations as errors or as acceptable conditions. 例如,如果将 15 个字符的字符串插入只有一个字符宽度的列,您可以截断该字符串。For example, if you are inserting a 15-character string into a column that is only one character wide, you can elect to truncate the string.

选择一个错误处理选项Select an error handling option

可以配置源、转换和目标处理错误和截断的方式。You can configure how sources, transformations, and destinations handle errors and truncations. 下表对这些选项进行说明:The following table describes the options.

选项Option 说明Description
组件失败Fail Component 发生错误或截断时数据流任务失败。The Data Flow task fails when an error or a truncation occurs. 失败是错误或截断的默认选项。Failure is the default option for an error and a truncation.
忽略失败Ignore Failure 忽略错误或截断,并且将数据行定向到转换或源的输出。The error or the truncation is ignored and the data row is directed to the output of the transformation or source.
重定向行Redirect Row 将错误或截断的数据行定向到源、转换或目标的错误输出。The error or the truncation data row is directed to the error output of the source, transformation, or destination.

获取有关错误的详细信息Get more info about the error

除数据列外,错误输出还包含 ErrorCode 列和 ErrorColumn 列。In addition to the data columns, the error output includes the ErrorCode and ErrorColumn columns. ErrorCode 列标识错误,而 ErrorColumn 列则包含错误列的沿袭标识符。The ErrorCode column identifies the error and the ErrorColumn contains the lineage identifier of the error column.

在某些环境下, ErrorColumn 列的值会设置为零。Under some circumstances, the value of the ErrorColumn column is set to zero. 当错误条件影响到整行而不是单列时,就会发生该情况。This occurs when the error condition affects the entire row instead of a single column. 例如,在查找转换中的查找失败时。An example is when a lookup fails in the Lookup transformation.

如果没有相应的错误说明和列名,这两个数值可能没有多大用处。These two numeric values may be of limited use without the corresponding error description and column name. 下面是一些用于获取错误说明和列名的方法。Here are some ways to get the error description and column name.

  • 可以通过将“数据查看器”附加到错误输出查看错误说明和列名。You can see both error descriptions and column names by attaching a Data Viewer to the error output. 在 SSIS 设计器中,右键单击通向错误输出的红色箭头,然后选择“启用数据查看器”。 In SSIS Designer, right-click on the red arrow leading to an error output and select Enable Data Viewer.

  • 可以通过启用日志记录并选择 DiagnosticEx 事件来查找列名称。You can find column names by enabling logging and selecting the DiagnosticEx event. 此事件将数据流列映射写入日志。This event writes a data flow column map to the log. 然后可以在此列映射中从其标识符查找列名称。You can then look up the column name from its identifier in this column map. 请注意,为了缩减日志大小, DiagnosticEx 事件不在其 XML 输出中保留空白。Note that the DiagnosticEx event does not preserve whitespace in its XML output to reduce the size of the log. 若要提高可读性,请将日志复制到支持 XML 格式和语法突出显示的 XML 编辑器中 - 例如 Visual Studio 中的 XML 编辑器。To improve readability, copy the log into an XML editor - in Visual Studio, for example - that supports XML formatting and syntax highlighting. 有关日志记录的详细信息,请参阅 Integration Services (SSIS) 日志记录For more info about logging, see Integration Services (SSIS) Logging.

    下面是数据流列映射的示例。Here is an example of a data flow column map.

    
    \<DTS:PipelineColumnMap xmlns:DTS="www.microsoft.com/SqlServer/Dts">  
        \<DTS:Pipeline DTS:Path="\Package\Data Flow Task">  
            \<DTS:Column DTS:ID="11" DTS:IdentificationString="ADO NET Source.Outputs[ADO NET Source Output].Columns[Customer]"/>  
            \<DTS:Column DTS:ID="12" DTS:IdentificationString="ADO NET Source.Outputs[ADO NET Source Output].Columns[Product]"/>  
            \<DTS:Column DTS:ID="13" DTS:IdentificationString="ADO NET Source.Outputs[ADO NET Source Output].Columns[Price]"/>  
            \<DTS:Column DTS:ID="14" DTS:IdentificationString="ADO NET Source.Outputs[ADO NET Source Output].Columns[Timestamp]"/>  
            \<DTS:Column DTS:ID="20" DTS:IdentificationString="ADO NET Source.Outputs[ADO NET Source Error Output].Columns[Customer]"/>  
            \<DTS:Column DTS:ID="21" DTS:IdentificationString="ADO NET Source.Outputs[ADO NET Source Error Output].Columns[Product]"/>  
            \<DTS:Column DTS:ID="22" DTS:IdentificationString="ADO NET Source.Outputs[ADO NET Source Error Output].Columns[Price]"/>  
            \<DTS:Column DTS:ID="23" DTS:IdentificationString="ADO NET Source.Outputs[ADO NET Source Error Output].Columns[Timestamp]"/>  
            \<DTS:Column DTS:ID="24" DTS:IdentificationString="ADO NET Source.Outputs[ADO NET Source Error Output].Columns[ErrorCode]"/>  
            \<DTS:Column DTS:ID="25" DTS:IdentificationString="ADO NET Source.Outputs[ADO NET Source Error Output].Columns[ErrorColumn]"/>  
            \<DTS:Column DTS:ID="31" DTS:IdentificationString="Flat File Destination.Inputs[Flat File Destination Input].Columns[Customer]"/>  
            \<DTS:Column DTS:ID="32" DTS:IdentificationString="Flat File Destination.Inputs[Flat File Destination Input].Columns[Product]"/>  
            \<DTS:Column DTS:ID="33" DTS:IdentificationString="Flat File Destination.Inputs[Flat File Destination Input].Columns[Price]"/>  
            \<DTS:Column DTS:ID="34" DTS:IdentificationString="Flat File Destination.Inputs[Flat File Destination Input].Columns[Timestamp]"/>  
        \</DTS:Pipeline>  
    \</DTS:PipelineColumnMap>  
    
    
  • 还可使用脚本组件将错误说明和列名称包含在错误输出的其他列中。You can also use the Script component to include the error description and the column name in additional columns of the error output. 有关示例,请参阅 使用脚本组件增强错误输出For an example, see Enhancing an Error Output with the Script Component.

    可以从想要捕获其错误的数据流组件的下游的任意位置将脚本组件添加到数据流的错误段。You can add the Script component to the error segment of the data flow anywhere downstream from the data flow components whose errors you want to capture. 通常应在错误行写入到目标之前立即放入脚本组件。Typically you place the Script component immediately before the error rows are written to a destination. 这样,脚本只查找已写入的错误行的说明。This way, the script looks up descriptions only for error rows that are written. 数据流的错误段可能纠正某些错误,并且不将这些行写入错误目标。The error segment of the data flow may correct some errors and not write those rows to an error destination.

另请参阅See Also

数据流 Data Flow
使用转换对数据进行转换 Transform Data with Transformations
使用路径连接组件 Connect Components with Paths
数据流任务 Data Flow Task
数据流Data Flow