SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SSIS Skip bad record
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

albertkohl
Aged Yak Warrior

USA
740 Posts

Posted - 02/19/2010 :  15:25:03  Show Profile  Visit albertkohl's Homepage  Reply with Quote
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

India
52325 Posts

Posted - 02/20/2010 :  00:26:12  Show Profile  Reply with Quote
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/

Go to Top of Page

albertkohl
Aged Yak Warrior

USA
740 Posts

Posted - 02/20/2010 :  15:17:13  Show Profile  Visit albertkohl's Homepage  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/21/2010 :  03:22:29  Show Profile  Reply with Quote
see

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

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

Go to Top of Page

subhash chandra
Starting Member

40 Posts

Posted - 02/21/2010 :  06:20:56  Show Profile  Reply with Quote
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/

Edited by - subhash chandra on 02/21/2010 06:21:38
Go to Top of Page

albertkohl
Aged Yak Warrior

USA
740 Posts

Posted - 02/21/2010 :  13:37:25  Show Profile  Visit albertkohl's Homepage  Reply with Quote
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!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/22/2010 :  08:28:29  Show Profile  Reply with Quote
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/

Go to Top of Page

albertkohl
Aged Yak Warrior

USA
740 Posts

Posted - 02/22/2010 :  11:10:53  Show Profile  Visit albertkohl's Homepage  Reply with Quote
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?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/22/2010 :  11:12:27  Show Profile  Reply with Quote
does delimiter be constant throughout?

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

Go to Top of Page

albertkohl
Aged Yak Warrior

USA
740 Posts

Posted - 02/22/2010 :  11:21:39  Show Profile  Visit albertkohl's Homepage  Reply with Quote
far as i can tell so far, yes.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/22/2010 :  11:26:04  Show Profile  Reply with Quote
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/

Go to Top of Page

albertkohl
Aged Yak Warrior

USA
740 Posts

Posted - 02/22/2010 :  11:30:58  Show Profile  Visit albertkohl's Homepage  Reply with Quote
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.
Go to Top of Page

albertkohl
Aged Yak Warrior

USA
740 Posts

Posted - 02/22/2010 :  13:42:17  Show Profile  Visit albertkohl's Homepage  Reply with Quote
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?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/23/2010 :  09:35:33  Show Profile  Reply with Quote
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/

Go to Top of Page

albertkohl
Aged Yak Warrior

USA
740 Posts

Posted - 02/23/2010 :  10:40:01  Show Profile  Visit albertkohl's Homepage  Reply with Quote
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
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 02/23/2010 :  18:13:15  Show Profile  Reply with Quote
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).
Go to Top of Page

albertkohl
Aged Yak Warrior

USA
740 Posts

Posted - 02/24/2010 :  00:39:56  Show Profile  Visit albertkohl's Homepage  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000