Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
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 '11 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 '11 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. –  Mark Storey-Smith Nov 9 '11 at 0:45
add comment

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.

share|improve this answer
    
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 '11 at 20:50
add comment

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

share|improve this answer
    
Thanks @Surfer513. There are no such options on Data Flow. They only exist on Control flow? –  flybyte Oct 7 '11 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. –  Thomas Stringer Oct 7 '11 at 9:31
    
Thanks I've added my screenshot of Data Flow Task –  flybyte Oct 7 '11 at 20:52
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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