Errors not being ignored

Question

Errors not being ignored

Evil Kraig F

SSC Guru

Points: 100851
More actions
February 17, 2012 at 2:50 pm

#255926

GRRRRRRRRRRRRRRRRRRRRRRRRRRRRRRrrrrrrrrrrrrrrrrr.
Alright, now that that's out of my system, I need a hand. Can someone explain to me why my error is not ignored when set to ignored?
Error:
Information: 0x402090DF at Data Flow Task, OLE DB Destination [16]: The final commit for the data insertion has started.
Error: 0xC0202009 at Data Flow Task, OLE DB Destination [16]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "The statement has been terminated.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Cannot insert the value NULL into column 'RedactedColumnA', table 'RedactedTargetTable'; column does not allow nulls. INSERT fails.".
Information: 0x402090E0 at Data Flow Task, OLE DB Destination [16]: The final commit for the data insertion has ended.
Seriously. What the !@#!#!@#!$!@#$!#!!!!! is the point of ignore error if it still errors?!!!
Yes, I know workarounds, what I want to know is why the heck this even happened. If nothing else, thanks for reading my vent. I'm just hoping someone can explain to me, or point me at a thorough explanation, of why an error wasn't ignored.
- Craig Farrell
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Viewing 10 posts - 1 through 9 (of 9 total)

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

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

Well, it's because it is not the OLE DB Destination itself that is having an error, it is the database engine that is throwing back an error.

What is the difference? Ignore failure dictates that failure is ignored (d'uh :-)) and that the row is still added to the output of the transformation. Hence, the row is still sent to the database engine. Where it violates the NOT NULL constraint on a column, so SQL Server cannot insert that row and thus giving back an error.

To solve this, you need to use redirect row so you get rid of this bad row so that SQL Server doesn't throw a tantrum.

ps: kind of sucks that you had to load about 173 million rows to find out Ignore Failure doesn't work 😀

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Charmer SSChampion Points: 14903 More actions · Answer 2

If Ignore Failure does not work exactly....then why should we have a such condition...or when will it work if we do Ignore Failure..?

Thanks,
Charmer

Phil Parkin SSC Guru Points: 246973 More actions · Answer 3

Charmer (2/21/2012)
If Ignore Failure does not work exactly....then why should we have a such condition...or when will it work if we do Ignore Failure..?

Please reread Koen's response.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Charmer SSChampion Points: 14903 More actions · Answer 4

Phil Parkin (2/21/2012)
Charmer (2/21/2012)
If Ignore Failure does not work exactly....then why should we have a such condition...or when will it work if we do Ignore Failure..?
Please reread Koen's response.

Hi Phil,

I thought Ignore Failure does not allow rows failing table conditions...but koen told that it still tries to insert into the destination....it seems what i thought was not correct... so how can i restrict certain error rows...?

Thanks,
Charmer

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 5

Charmer (2/21/2012)
Phil Parkin (2/21/2012)
Charmer (2/21/2012)
If Ignore Failure does not work exactly....then why should we have a such condition...or when will it work if we do Ignore Failure..?
Please reread Koen's response.
Hi Phil,
I thought Ignore Failure does not allow rows failing table conditions...but koen told that it still tries to insert into the destination....it seems what i thought was not correct... so how can i restrict certain error rows...?

A column containing a NULL value does not make the OLE DB Destination fail, it is the database engine that throws an error. Try redirecting error rows to another destination.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Charmer SSChampion Points: 14903 More actions · Answer 6

Koen Verbeeck (2/21/2012)
Charmer (2/21/2012)
Phil Parkin (2/21/2012)
Charmer (2/21/2012)
If Ignore Failure does not work exactly....then why should we have a such condition...or when will it work if we do Ignore Failure..?
Please reread Koen's response.
Hi Phil,
I thought Ignore Failure does not allow rows failing table conditions...but koen told that it still tries to insert into the destination....it seems what i thought was not correct... so how can i restrict certain error rows...?
A column containing a NULL value does not make the OLE DB Destination fail, it is the database engine that throws an error. Try redirecting error rows to another destination.

Hi Koen,

i agree with you , that we have an option to redirect it...but what if we created a package and working on the production server...? i mean i already created a package with ignore failure and its been running on the production server...if it is going to fail due to null values(which i don't want to), i just want to ignore those null value rows...in this case what should i do rather than redirecting it to a new destination...? If my question was stupid, please forgive me...

Thanks,
Charmer

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 7

Charmer (2/21/2012)
Hi Koen,
i agree with you , that we have an option to redirect it...but what if we created a package and working on the production server...? i mean i already created a package with ignore failure and its been running on the production server...if it is going to fail due to null values(which i don't want to), i just want to ignore those null value rows...in this case what should i do rather than redirecting it to a new destination...? If my question was stupid, please forgive me...

You should check for NULL values in the dataflow or at the source before you write them to a destination. Relying on the Ignore Failure of SSIS is apparently as Craig found out not 100% reliable.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Charmer SSChampion Points: 14903 More actions · Answer 8

Koen Verbeeck (2/21/2012)
You should check for NULL values in the dataflow or at the source before you write them to a destination. Relying on the Ignore Failure of SSIS is apparently as Craig found out not 100% reliable.

so conditional split...ok Koen..Thanks for the information..

Thanks,
Charmer

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 9

Koen,

Thanks for the information. I think I puzzled out why the engine and not the component was complaining. Since the column wasn't mapped inside the component, the component didn't test for failure and thus relied on the engine for it. An oversight on my part but still, at least it explains what happened. That column wasn't even supposed to end up in the staging table.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA