Fast load error output doesn't redirect entire batch

0
votes

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.

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?

1
sql-serverssisetlssis-2014oledbdestination
I think it will depend on the error. If on the client side (e.g. field data type conversion), the record can be redirected but in the case of a server-side error, the entire batch will be rolled back. – Dan Guzman
@DanGuzman what you mentioned is 100% true. I posted a detailed answer about that. – Hadi

1 Answers

1
votes

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

enter image description here

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.