SSIS - OLE DB Destination Fast load error output doesn't redirect entire batch
Problem:
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 didn't find any similar case online. Any explanation?
Solution:
There are 2 phases of validation for the data when it comes to the OLE DB Destination:
- Client-side validation
- 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.
Comments
Post a Comment