快速加载错误输出不会重定向整个批次 [英] Fast load error output doesn't redirect entire batch

问题描述

上下文

我正在开发一个简单的SSIS包，其中包含带有以下内容的数据流任务:

• 1个OLE DB源
• 2个查找转换
• 1个OLE DB目标
• 错误输出行的1个OLE DB目标

我在OLE DB目标中都使用了FastLoad选项，并且我已经配置了第一个错误输出以将行重定向到第二个目标.

问题

从许多在线文章中，我读到使用Fastload选项将导致整个批处理失败，不仅错误的行被重定向.

• 当我们说Fastload选项分批加载数据时，我们正在谈论的是将数据从OLE DB目标输入列发送到目标本身(数据库引擎)的阶段

  2.服务器端验证

  在将数据插入到目标(例如身份，主键或外键违规...)时，将执行这种类型的验证.

  如果在此阶段发生错误，则将拒绝整个批次，并将所有行重定向到错误输出.

  Context

  I am developing a simple SSIS package that contains a Data flow Task with:

  • 1 OLE DB Source
  • 2 Lookup Transformations
  • 1 OLE DB Destination
  • 1 OLE DB Destination for error output rows

  I am using the FastLoad option in both OLE DB Destinations and i have configured the error output of the first one to redirect rows to the second destination.

  Question

  From many online articles, i read that using Fastload option will cause the entire batch to fail an not only erroneous rows are redirected.

  • Error Handling With OLE DB Destinations
  • Error output in OLE DB Destination. How to redirect a row?
  • How to handle Failed Rows in a Data Flow
  • Have your SSIS Fast Load (Bulk Load) and Row by Row Error Messages too

  But when executed the package only 2 rows are redirected and all other rows are imported successfully. And i checked that the sum of the rows count in both destination is equal to the source row count which means that only erroneous rows are redirected.

  Note that:

  • Max Commit size = 2147483647
  • Batch size is empty
  • Table lock and check constraints option are checked
  • I am using SQL Server 2014 with Visual Studio 2013

  I didn't find any similar case online. Any explanation?

  解决方案

  What @DanGuzman mentioned is true, since there are 2 phases of validation for the data when it comes to the OLE DB Destination:

  1. Client-side validation
  2. Server-side validation:

  1. The Client-side validation:

  When data from the pipeline to the OLE DB destination the pipeline columns (External columns) are mapped to the OLE DB Destination Input columns which must have a data type relevant to the server side columns data types (Database Engine). If an error occured While data is passed from External columns to the OLE DB destination inputs columns the error row can be redirected alone.

  Example: Implicit conversion failure: When a DT_STR field is mapped to a DT_DATE and it contains an invalid date value

  When we say that Fastload option load data in batches we are talking about the phases when data are sent from the OLE DB destination input columns to the destination itself (Database engine)

  2. Server-side validation

  This type of validation is done when inserting data to the destination such as Identity, primary key or foreign key violation ...

  If an error occurs in this phase the whole batch is rejected and all rows are redirected to the error output.

  这篇关于快速加载错误输出不会重定向整个批次的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！