I've a SSIS Data Flow Task. Within which i've souce flat file and OLEDB destination. I've an error output sent to redirect row on OLEDB Destination.

The problem is that the redirect row always run even though there's no error. Is this a bug with SSIS package? If someone could help, will be appreciated.

e.g. enter image description here

  • 4
    Oh, yes, it should be green an no, it's not a bug. It is successfully executing the downstream components but no row are flowing through so no worries. You can see the 1 rows leading from (out of screen) to Insert on to Device Table but nothing on the error path. Depending on what you're doing there, an easier pattern might be to attach a row count variable instead of your script task and then in the PostExecute event handler for the DataFlow check the value of the variable. If it's not 0, you had N rows fail.
    – billinkc
    Oct 8, 2011 at 1:19
  • thanks @billinkc, your suggestion makes perfect sense. My main concern was creating an empty file whether or not there are n number of errors. I will follow you suggestion and use RowCount. if RowCount <1 then delete the empy file
    – flybyte
    Oct 9, 2011 at 2:50
  • 3
    @billinkc It sounds like your comment is a correct answer. Can I suggest you either edit your earlier answer or add a fresh one so it can be voted for. Nov 9, 2011 at 0:45

2 Answers 2


I am looking for the article that describes this behaviour better than I'll do here and will update if I can find it. I thought it was by Kirk Haselden or John Welch but no so luck.

If the batch size is set to 0, default, either the entire data flow will make it in or none of it. Generally, this is what you want as it gives the best performance and consistency.

If you have an exception in in the commit size (full or otherwise) and have set redirect error rows, the error row(s) and all the valid ones will all be mingled in the exception flow.

If your desire is to get all to "good" rows in and spit the "bad" ones to a staging area, the pattern described in the article is to try and commit successively smaller batches into the destination table. Eventually, you'll partition the inserts down to a valid sized batch or find the offending row(s) which are then stored to a staging area for review/cleanup.

The implementation is to have serial error redirect OLE DB destinations with decreasing rows per batch commit sizes, starting at say 10,000 and having the penultimate one with a size of 1. The last OLE DB destination would be to the staging/error table.

enter image description here

All of this assumes that you're using "fast load" version for the OLE provider. If you're "table or view" or "table or view from variable" you're already doing singleton inserts (and probably killing your performance) so the redirect stuff isn't needed to find a bad row.

  • Thanks @billinkc, I tried with fast load. The "OLEDB Destination Error Output" always gets executed with GREEN colour irrespective of ERROR or SUCCESS. I tried to use FAST LOAD and no luck.
    – flybyte
    Oct 7, 2011 at 20:50

Do you have it set so that it runs ON COMPLETION or ON ERROR? ON COMPLETION runs whether there is success or failure.

  • Thanks @Surfer513. There are no such options on Data Flow. They only exist on Control flow?
    – flybyte
    Oct 7, 2011 at 4:07
  • @flybyte let me play with it in a little bit. I've done a bit of IS and it is just escaping me right now. Oct 7, 2011 at 9:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.