Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SSIS - WRITING OUT BAD ROWS FROM THE SOURCE COMPONENT Expand / Collapse
Author
Message
Posted Monday, December 13, 2010 1:33 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 23, 2014 8:43 AM
Points: 16, Visits: 82
Consistently, my source flat file that I import daily will have a bad record in it. In the past, I had my packages write that bad row out and continue on or fail. Since SSIS had horrible error messaging to try and figure out what row is bad, I want it to write it out to another file.

I tried redirecting the error to a flat file but when it fails, it doesn't write anything.

What am I missing?

Thanks!
Post #1034058
Posted Monday, December 13, 2010 4:01 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 11:37 AM
Points: 124, Visits: 487
Is 64 bit Oracle provider installed?
Post #1034121
Posted Monday, December 13, 2010 4:20 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 23, 2014 8:43 AM
Points: 16, Visits: 82
No, it's SQL 2005 SSIS and importing a flat file. The file sometimes has bad rows such as missing columns or bad characters. I have a flat file connection specified for the file that I import so if it fails that, I want that record exported out and to continue on the import.

On the other side of the import, if the import into the table fails, I want to see the row it fails by writing out to another file and continuing on the import.

This also allows me to just import the affected row once it's fixed so I don't have to re-import the full file since it's quite large
Post #1034124
Posted Monday, December 13, 2010 5:22 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:23 AM
Points: 6,137, Visits: 7,187
What are you using to redirect the error?

I personally would use a conditional split with a bunch of rules to send any failed item to a separate stream, and then drop the 'failed' stream to an error file.



- 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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1034134
Posted Monday, December 13, 2010 5:25 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 23, 2014 8:43 AM
Points: 16, Visits: 82
Ok - Do I have to know the specific issues that I would be looking for in order to use that?
Post #1034137
Posted Monday, December 13, 2010 5:38 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 11:37 AM
Points: 124, Visits: 487
My apologies, I replied to the wrong thread You need to give us more specifics. What is happening when you are running your package? Are rows erroring to your file but nothing is getting written? Or are rows not erroring to begin with? need more detail. And yes, you would need to know what you are looking for with a conditional split.
Post #1034138
Posted Monday, December 13, 2010 8:09 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 23, 2014 8:43 AM
Points: 16, Visits: 82
My test file has a couple rows in it that have bad characters in it. I have a transfer task that has a flat file source connection with all the columns specified in it.

I was setting my Error Output to redirect for all the columns if there is an error and putting another file connection for the records to be redirected to.

When I would run it, the flat file source task would fail but nothing would get written to it.
Post #1034159
Posted Tuesday, December 14, 2010 8:49 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 11:37 AM
Points: 124, Visits: 487
Sounds like the error is occuring before runtime. Which means that the package hasn't read your source so therefore can't write anything that errors.

Post the error message you are receiving and hopefully we can help you figure out the problem.
Post #1034498
Posted Tuesday, December 14, 2010 11:49 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 23, 2014 8:43 AM
Points: 16, Visits: 82
Here is the error I am getting.

Error: An error occurred while processing file "filename" on data row 5.
SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "x"(1) returned error code 0xC0202092. The component returned failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code
SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038
SSIS Error Code DTS_E_THREADFAILED.

I know it tells me a row but that's not what I want. I want to write out the bad row to another file.
Post #1034628
Posted Wednesday, December 15, 2010 6:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 8, 2014 5:36 AM
Points: 5, Visits: 98
Hi ,

The error usually occurs when the flat file source has data conversion issues.

Please check for the data types which you are using in the flat file and see if there is any mismatch.


Post #1035078
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse