I'm confused about how the Error Output is handled on OLE Destinations using the various load types. From what I've read, this is how it is supposed to behave:1) Fast Load, Maximum insert commit size = 0: This will not allow Error Output; Must either turn off Fast Loading, or set a max insert commit.2) Fast Load, Maximum insert commit size > 1: This will allow Error Output to be redirected, but if one record fails in a batch of N records (where N = max insert commit), all N records are redirected.3) Not Fast Load: This will only redirect error output for the failed recordsSo I understand 1 and 3. However, #2 seems to behave differently from what I read. It seems that if you set a max insert commit to, say, 1000 records, it will still only redirect the failed records (based on the count being redirected from the OLE destination). I never see multiples of N coming out of the destination.Can someone clear this up, once and for all?Thanks in advance.Jerad

If your commit size is 1000 - if any one record in those 1000 is an error record, all the 1000 records are re directed to the error output.Please see this discussion -http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/d7934c9a-a222-4f03-a5f2-75c6bebb47d7/To get around this - you need to use another OLE DB destination for the error records with a commit size of 1 and redirect error record.so your design will be like this -1) Load to the destination with a commit size of 10002) Any Error records from the step(1) should be loaded to destination witha commit size of 1 3) capture error records from step(2)

JayKun said:If your commit size is 1000 - if any one record in those 1000 is an error record, all the 1000 records are re directed to the error output.But that's what I'm saying -- this is what everyone else says, but this doesn't appear to be the case. I understand exactly what you're saying, and this is consistent with everything else I read. But, as you'll see below, it doesn't work like this.See these screenshots:Data Flow Slow Destination SettingsFast Destination SettingsIn the data flow, you'll see that many records (over 17,000) come into the Fast Destination, and while it's set up with a max insert commit size of 1,000, only 86 records are redirected out of it -- the same number that are redirected out of the Slow Destination. So, it seems that this serves no purpose -- I might as well just let my Fast Destination handle my error redirects, and log them straight from there, and get rid of the Slow Destination.Am I missing something?

Update: I have just confirmed that using a fast load and setting MICS to a non-zero value, will *not* result in redirecting the entire buffer to the error output -- it will only redirect the error rows.See this flow for verification:Data Flow 2Surely I'm not the first person to discover this? I've searched on this topic several times, and have never found anything saying this to be the case. All blogs and forum discussions say that you must user two destinations -- one fast using MICS, and one slow -- to trap destination errors.Again, am I misunderstanding something? I just can't believe that all of these blogs and discussions would be wrong.Thanks again.Jerad

I can see in your screen shots that entite batch is not being redirected. But that was not how I understood. Could you post some of the links that you found?.According to thishttp://agilebi.com/cs/blogs/jwelch/archive/2008/09/05/error-redirection-with-the-ole-db-destination.aspx"If you set this to a value of one, the OLE DB Destination will only try to commit a single row at time. The problem with this is that single row inserts are painfully slow. So you probably want to set this value considerably higher, between 10,000 and 100,000 rows. You may have to try a few different values to determine what works best in your environment. Now that you have the OLE DB Destination set up to commit multiple, smaller batches instead of one huge batch, you can enable error redirection. However, it will still redirect the entire batch of records that contain an error, not just the individual rows in error. For example, if you defined a batch size of 10,000 rows, and one row in the 10,000 row batch has an error, they will all be redirected to the error output. So how do you set it up to get all the good rows inserted, and get down to just the rows in error? We really want it to work on single row batches for error handling purposes, but as pointed out earlier, that can be very slow. "

Pretty much any discussion you can find on the topic will say this. That's why I'm so baffled -- how can all of these people be wrong -- and I'm still not convinced that I'm not doing something wrong, that's why I'd like someone else to confirm this.Here are some other discussions:http://www.intermz.com/blog/2008/04/08/ssis-solving-load-performance-vs-row-level-error-handling/http://blog.cybner.com.au/2008/03/optimising-ssis-for-large-data-loads.htmlhttp://www.mombu.com/microsoft/sql-server-data-warehousing/t-cannot-insert-rows-to-fact-table-and-processing-does-not-end-676232.htmlhttp://weblogs.sqlteam.com/jamesn/archive/2008/02/13.aspxhttp://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/b2d08f58-5790-4e54-93db-86c5ff8edf32/http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/9ea43b65-3584-4dd1-9e2f-5aeb706a061a/

Is there anyone else that can confirm or deny that the above is true?

I too have seen the same behaviour and I'm just as puzzled as you. Have you had any luck finding an answer elsewhere? I'm using SSIS on a 2008 R2.