SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Redirect error rows to another table using SSIS


Redirect error rows to another table using SSIS

Author
Message
apatel 89420
apatel 89420
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 21
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??
GSquared
GSquared
SSC Guru
SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)

Group: General Forum Members
Points: 58995 Visits: 9730
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
apatel 89420
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 21
We just have the 'red' arrow going to error table, listed as OLE DB Destination Error Output. Is this what you mean?
GSquared
GSquared
SSC Guru
SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)

Group: General Forum Members
Points: 58995 Visits: 9730
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
apatel 89420
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 21
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
sam.dahl
SSC Eights!
SSC Eights! (852 reputation)SSC Eights! (852 reputation)SSC Eights! (852 reputation)SSC Eights! (852 reputation)SSC Eights! (852 reputation)SSC Eights! (852 reputation)SSC Eights! (852 reputation)SSC Eights! (852 reputation)

Group: General Forum Members
Points: 852 Visits: 887
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
apatel 89420
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 21
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
sam.dahl
SSC Eights!
SSC Eights! (852 reputation)SSC Eights! (852 reputation)SSC Eights! (852 reputation)SSC Eights! (852 reputation)SSC Eights! (852 reputation)SSC Eights! (852 reputation)SSC Eights! (852 reputation)SSC Eights! (852 reputation)

Group: General Forum Members
Points: 852 Visits: 887
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search