Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Redirect error rows to another table using SSIS Expand / Collapse
Author
Message
Posted Thursday, October 20, 2011 6:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 22, 2011 11:08 AM
Points: 8, 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??
Post #1193618
Posted Thursday, October 20, 2011 7:18 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 1:09 PM
Points: 13,872, Visits: 9,597
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
Post #1193650
Posted Thursday, October 20, 2011 7:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 22, 2011 11:08 AM
Points: 8, Visits: 21
We just have the 'red' arrow going to error table, listed as OLE DB Destination Error Output. Is this what you mean?
Post #1193662
Posted Thursday, October 20, 2011 7:28 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 1:09 PM
Points: 13,872, Visits: 9,597
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
Post #1193671
Posted Thursday, October 20, 2011 7:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 22, 2011 11:08 AM
Points: 8, 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?
Post #1193677
Posted Thursday, October 20, 2011 5:40 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 8:23 PM
Points: 367, Visits: 822
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.
Post #1194079
Posted Friday, October 21, 2011 6:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 22, 2011 11:08 AM
Points: 8, 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.
Post #1194323
Posted Sunday, October 23, 2011 4:32 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 8:23 PM
Points: 367, Visits: 822
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.
Post #1194993
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse