I'm using SSIS and i'm getting a FK violation in my OLE DB Destination task. I'm getting a FK violation and would like to ignore any rows (throw them away) where the error is encountered. The Destination objects doesn't seem able to deal with specific kinds of errors (only able to ignore all errors for example), and i can't get the conditional split task to test for FK violations.
Does anyone know how to throw these rows away that hit the FK violation? I want the container to still succeed, and just throw bad rows away. If other 'real' errors are encountered i want the package to fail.
Let me know if i could include more info to clarify.
On 6-Nov-2009, Dave <djohanns...@gmail.com> wrote:
> I'm using SSIS and i'm getting a FK violation in my OLE DB Destination > task. I'm getting a FK violation and would like to ignore any rows > (throw them away) where the error is encountered. The Destination > objects doesn't seem able to deal with specific kinds of errors (only > able to ignore all errors for example), and i can't get the > conditional split task to test for FK violations.
> Does anyone know how to throw these rows away that hit the FK > violation? I want the container to still succeed, and just throw bad > rows away. If other 'real' errors are encountered i want the package > to fail.
> Let me know if i could include more info to clarify.
Are you using bulk loading (called "fast load" in SSIS, I believe) in your OLE DB Destination adapter? I don't think you can track particular errors with bulk loading on. If you turn off "Fast Load", the adapter will have an error path you can use to redirect error rows elsewhere. The error path adds "Error Description" and "Error Code" columns to the flow, which you can interrogate for FK violations if you needed to do something more with that type of error. The big downside to this approach is that performance will be miserable for a large dataset (because bulk loading will have been turned off).
> On 6-Nov-2009, Dave <djohanns...@gmail.com> wrote:
> > I'm using SSIS and i'm getting a FK violation in my OLE DB Destination > > task. I'm getting a FK violation and would like to ignore any rows > > (throw them away) where the error is encountered. The Destination > > objects doesn't seem able to deal with specific kinds of errors (only > > able to ignore all errors for example), and i can't get the > > conditional split task to test for FK violations.
> > Does anyone know how to throw these rows away that hit the FK > > violation? I want the container to still succeed, and just throw bad > > rows away. If other 'real' errors are encountered i want the package > > to fail.
> > Let me know if i could include more info to clarify.
> Are you using bulk loading (called "fast load" in SSIS, I believe) in your > OLE DB Destination adapter? I don't think you can track particular errors > with bulk loading on. If you turn off "Fast Load", the adapter will have > an > error path you can use to redirect error rows elsewhere. The error path > adds "Error Description" and "Error Code" columns to the flow, which you > can > interrogate for FK violations if you needed to do something more with that > type of error. The big downside to this approach is that performance will > be miserable for a large dataset (because bulk loading will have been > turned > off).
You'll have to test it, but the error code for integrity violations is -1071607683 in the OLE DB destination adapter, I believe.
> On 6-Nov-2009, "mouser" <inva...@invalid.com> wrote:
> > On 6-Nov-2009, Dave <djohanns...@gmail.com> wrote:
> > > I'm using SSIS and i'm getting a FK violation in my OLE DB Destination > > > task. I'm getting a FK violation and would like to ignore any rows > > > (throw them away) where the error is encountered. The Destination > > > objects doesn't seem able to deal with specific kinds of errors (only > > > able to ignore all errors for example), and i can't get the > > > conditional split task to test for FK violations.
> > > Does anyone know how to throw these rows away that hit the FK > > > violation? I want the container to still succeed, and just throw bad > > > rows away. If other 'real' errors are encountered i want the package > > > to fail.
> > > Let me know if i could include more info to clarify.
> > Are you using bulk loading (called "fast load" in SSIS, I believe) in your > > OLE DB Destination adapter? I don't think you can track particular errors > > with bulk loading on. If you turn off "Fast Load", the adapter will have > > an > > error path you can use to redirect error rows elsewhere. The error path > > adds "Error Description" and "Error Code" columns to the flow, which you > > can > > interrogate for FK violations if you needed to do something more with that > > type of error. The big downside to this approach is that performance will > > be miserable for a large dataset (because bulk loading will have been > > turned > > off).
> You'll have to test it, but the error code for integrity violations is > -1071607683 in the OLE DB destination adapter, I believe.- Hide quoted text -
> - Show quoted text -
i think i figured it out! Using the lookup task just before the destination adapter one can check for FK violations (via tailored query) and then throw non matching rows away, and send matching rows (where FK rows are found in lookup / reference table) to the destination adapter for insertion.
On 6-Nov-2009, Dave <djohanns...@gmail.com> wrote:
> i think i figured it out! Using the lookup task just before the > destination adapter one can check for FK violations (via tailored > query) and then throw non matching rows away, and send matching rows > (where FK rows are found in lookup / reference table) to the > destination adapter for insertion.
> works like a charm.
That can work too, but beware of the lookup transformation pulling massive datasets into memory (unless you use partial caching).
On Nov 6, 6:57 pm, "mouser" <inva...@invalid.com> wrote:
> On 6-Nov-2009, Dave <djohanns...@gmail.com> wrote:
> > i think i figured it out! Using the lookup task just before the > > destination adapter one can check for FK violations (via tailored > > query) and then throw non matching rows away, and send matching rows > > (where FK rows are found in lookup / reference table) to the > > destination adapter for insertion.
> > works like a charm.
> That can work too, but beware of the lookup transformation pulling massive > datasets into memory (unless you use partial caching).
oh thanks, i'll consider this also. both pieces of info where very helpful!
|
