Redirect error rows to another table using SSIS

Question

Redirect error rows to another table using SSIS

apatel 89420

Mr or Mrs. 500

Points: 554
More actions
October 20, 2011 at 6:45 am

#148731

hi, i have a SSIS package that is currently processing about 3MM rows. The process is, there is a flat file received from a vendor, it is transformed with date format (etc), then loaded to a table. If there is an error of some sort, it is directed to error table. However, currently, if one row is an error record, the whole (i assume) batch of records get sent to error table while processing. I need to stop duplicate rows from processing, but also send those duplicate rows to error table. We already tried using IGNORE_DUP_KEY, which suffices what we are trying to do, but it does not send error (duplicated record line) records to the error table. We need the error records so that the users can reconcile the data. Can someone help??

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

In the error-handling settings for the data flow, what are the settings?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

apatel 89420 Mr or Mrs. 500 Points: 554 More actions · Answer 2

We just have the 'red' arrow going to error table, listed as OLE DB Destination Error Output. Is this what you mean?

GSquared SSC Guru Points: 260824 More actions · Answer 3

Open up the properties of your data destination. Go to "Error Output", and see what you have set as the "Error" property.

The options are:

Fail component

Ignore failure

Redirect row

"Fail component" will fail the whole step, and will direct the package down your "red arrow" path. "Ignore failure" will do exactly that, and will allow the step to succeed. What you want is "Redirect row".

Take a look at this for details: http://msdn.microsoft.com/en-us/library/ms141679.aspx

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

apatel 89420 Mr or Mrs. 500 Points: 554 More actions · Answer 4

Thanks. Yes, we have already set it to 'redirect row'. It is currently redirecting rows to the error table, but the issue is that it redirects like a 'batch' of rows. If there is one row that is duplicate/error row, about 6000 or so rows get moved to the error table at a time. Is there a way to elimate that and just move that ONE row to the error table?

sam.dahl Hall of Fame Points: 3112 More actions · Answer 5

IIRC, when a destination is set to use bulk operations the data loads in batches and any errors cannot be handled individually. You would have to turn off bulk operations, or use transformations prior to the destination load to do your error checking thus guaranteeing only valid data during the load.

apatel 89420 Mr or Mrs. 500 Points: 554 More actions · Answer 6

Thanks, but i was able to use this and resolve the problem. FYI, you can follow this if anyone else is running into the same issue.

http://agilebi.com/jwelch/2008/09/05/error-redirection-with-the-ole-db-destination/

My issue is now resolved.

sam.dahl Hall of Fame Points: 3112 More actions · Answer 7

Thanks for the link, I shall have to add that error handling approach to my list of favourites 🙂

I wonder if some settings might cause locking issues; probably only with transactions.