SSIS Skip bad record - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SSIS Skip bad record

Author

Topic

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2010-02-19 : 15:25:03

is there any way to have SSIS skip a bad record in a flat file?

for example, i'm trying to process about 40 million rows a client sent me, and there are rows in the text file that are for example short one column compared to the rest of the file.

i either wanna redirect these rows to another file, or simply skip them. i tried the flat > OLE with a fail to a raw text file, but it still crashes soon as it hits the first bad row.

here's my error:

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.
The PrimeOutput method on component "Flat File Source"
(1) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput().
The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
There may be error messages posted before this with more information about the failure.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-20 : 00:26:12

quote:
Originally posted by albertkohl

is there any way to have SSIS skip a bad record in a flat file?

for example, i'm trying to process about 40 million rows a client sent me, and there are rows in the text file that are for example short one column compared to the rest of the file.

i either wanna redirect these rows to another file, or simply skip them. i tried the flat > OLE with a fail to a raw text file, but it still crashes soon as it hits the first bad row.

here's my error:

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.
The PrimeOutput method on component "Flat File Source"
(1) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput().
The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
There may be error messages posted before this with more information about the failure.

put a conditional transformation task and check for presence of column. connect true part to current next tasks and use false part to redirect to error file

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2010-02-20 : 15:17:13

i assume your talking about a conditional split?

would you please give me an example based off 3 columns.

first_name
last_name
full_name

i'm not really following how this transformation works :( kinda new to ssis packages

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-21 : 03:22:29

see

http://blogs.techrepublic.com.com/datacenter/?p=212

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

subhash chandra
Starting Member

40 Posts

Posted - 2010-02-21 : 06:20:56

Hello Albert,

You can use the redirect row functionality of SSIS as described in blog: http://sqlreality.com/blog/ssis/redirect-error-rows-to-log-table/

Regards,
Subhash Chandra
Founder: http://SQLReality.com/blog/

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2010-02-21 : 13:37:25

visakh, i'm more interested in the code to test if that column exists would you be able to assist with that? (the expression it self)

and subhash, i think my problem is a little different because my error is in the Flat file source, and it's a column delimiter error, not a conversion or anything. let me know if im mistaken.

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-22 : 08:28:29

quote:
Originally posted by albertkohl

visakh, i'm more interested in the code to test if that column exists would you be able to assist with that? (the expression it self)

and subhash, i think my problem is a little different because my error is in the Flat file source, and it's a column delimiter error, not a conversion or anything. let me know if im mistaken.

Thanks!

do you mean to say your metadata may vary at run time?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2010-02-22 : 11:10:53

what i'm saying, is the flat file has some bad records in it. for example, some 99.9% of the data is:

FIRSTNAME,LASTNAME,FULLNAME

but some bad records would be:

FIRSTNAME,LASTNAME
or
FIRSTNAME,LASTNAME,FULLNAME,ADDRESS

those records are causing fatal column delimination errors, and i need to either re-direct them to an error file, or just get SSIS to ignore them, and move on to the next record.

Make sense?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-22 : 11:12:27

does delimiter be constant throughout?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2010-02-22 : 11:21:39

far as i can tell so far, yes.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-22 : 11:26:04

quote:
Originally posted by albertkohl

far as i can tell so far, yes.

then what you could do add derived column to get number of fields present (will be 1 + number of ,s if delimiter is consistent). then use this as in conditional check in conditional split.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2010-02-22 : 11:30:58

i see, that makes sense, i'm totally noobish w/ SSIS language though, would you be able to give me the expression?

i REALLY appreciate it.

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2010-02-22 : 13:42:17

also another note, i THINK the error is actually from the FLAT File, so if that's the case, it's erroring before it would even get to the derived transformation wouldnt it?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-23 : 09:35:33

quote:
Originally posted by albertkohl

also another note, i THINK the error is actually from the FLAT File, so if that's the case, it's erroring before it would even get to the derived transformation wouldnt it?

yup. that case it errors before.
whats the data shown for those cases when you do preview?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2010-02-23 : 10:40:01

alot of times, there are to many commas, so when it gets like 1 million records into the data, it sees an extra "column" and it craps out. but i want it just to tell that row to kick-rocks (because it's 99.9% garbage) and move on to next instead of just taking a big steamy poop... :D

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-02-23 : 18:13:15

I'm not an expert when it comes to SSIS and flat files but I think you only have two real options:
1. To fix the file before you try to load it.
2. Load each row as a single column and then parse it out in SSIS and handle bad rows however you like (throw away, redirect, etc).

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2010-02-24 : 00:39:56

DAMN! that's kinda what i was thinking the response would be....

well thanks for the help guys... i guess i'll just have to bite the bullet, and work based off that.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources