 |
|
|
|
Next: DB Owner
|
| Author |
Message |
External
Since: Jun 18, 2008
Posts: 7
|
(Msg. 1) Posted: Fri Nov 06, 2009 5:17 pm
Post subject: SSIS hitting FK violation - need to ignore the error rows - have
Archived from groups: microsoft>public>sqlserver>programming (more info?)
|
|
|
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.
thanks, dave |
|
| Back to top |
|
 | |
External
Since: Jun 18, 2008
Posts: 7
|
(Msg. 2) Posted: Fri Nov 06, 2009 6:44 pm
Post subject: Re: SSIS hitting FK violation - need to ignore the error rows - have [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
On Nov 6, 6:04 pm, "mouser" wrote:
> On 6-Nov-2009, "mouser" wrote:
>
>
>
>
>
> > On 6-Nov-2009, Dave 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.
works like a charm.
dave |
|
| Back to top |
|
| |
External
Since: Sep 30, 2008
Posts: 44
|
(Msg. 3) Posted: Fri Nov 06, 2009 9:25 pm
Post subject: Re: SSIS hitting FK violation - need to ignore the error rows - have package succeed [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
On 6-Nov-2009, Dave 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). >> Stay informed about: SSIS hitting FK violation - need to ignore the error rows .. |
|
| Back to top |
|
| |
External
Since: Sep 30, 2008
Posts: 44
|
(Msg. 4) Posted: Fri Nov 06, 2009 9:25 pm
Post subject: Re: SSIS hitting FK violation - need to ignore the error rows - have package succeed [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
On 6-Nov-2009, "mouser" wrote:
> On 6-Nov-2009, Dave 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. >> Stay informed about: SSIS hitting FK violation - need to ignore the error rows .. |
|
| Back to top |
|
| |
External
Since: Sep 30, 2008
Posts: 44
|
(Msg. 5) Posted: Fri Nov 06, 2009 10:25 pm
Post subject: Re: SSIS hitting FK violation - need to ignore the error rows - have package succeed [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
On 6-Nov-2009, Dave 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). >> Stay informed about: SSIS hitting FK violation - need to ignore the error rows .. |
|
| Back to top |
|
| |
External
Since: Jun 18, 2008
Posts: 7
|
(Msg. 6) Posted: Sat Nov 07, 2009 5:32 pm
Post subject: Re: SSIS hitting FK violation - need to ignore the error rows - have [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
On Nov 6, 6:57 pm, "mouser" wrote:
> On 6-Nov-2009, Dave 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! >> Stay informed about: SSIS hitting FK violation - need to ignore the error rows .. |
|
| Back to top |
|
| |
| Related Topics: | select between dates ignore error - i ve got below sp -------------------------------- ALTER PROCEDURE [dbo].[selectBetweenDays] @affid varchar(50), @firstdate varchar(8), @seconddate varchar(8) AS BEGIN SET NOCOUNT ON; SELECT * FROM sales WHERE payTIME >= @firstdate AND payTIME <...
How to trap and ignore 7357 error (UPDATE FoxPro table via.. - I have a stored procedure that updates a "Linked Server" FoxPro table using the OPENQUERY command. However the FoxPro OLEDB driver raises the above error, which is then passed to the SP, which is then fails the DTS package The SP runs: EXECU...
Bizarre error from SSIS package - Hello all: I have a package with a precedence constraint containing the expression: ISNULL(@endDate) == True (I have tried other variations, of course, since ISNULL(@endDate) is functionally equivalent). I have another precedence constraint of value: ...
Excluding rows that have no child rows in a hireachy type .. - Hi All, I have inherited a badly designed database. ANyway, I have a table that is losely based on a hirearchy type table l2,l3,l4, relate to the hirearchy. l2 is the parent, l3 is first node/child, l4 is secont node/ grand child. What I want is to..
Ignore Index - How do I specify in a SELECT statement not to use an index? I have a non-clustered index on a column and I do not want to use it so that I can demonstrate how long it will take to execute without the index. I do not want to drop and/or disable the.. |
|
You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum
|
|
|
|
|
|
|
FAQ
Search
Profile
Private Messages
Log in
