SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS - WRITING OUT BAD ROWS FROM THE SOURCE COMPONENT


SSIS - WRITING OUT BAD ROWS FROM THE SOURCE COMPONENT

Author
Message
OlyKLin
OlyKLin
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 126
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!
tmitchelar
tmitchelar
SSC Veteran
SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)

Group: General Forum Members
Points: 251 Visits: 508
Is 64 bit Oracle provider installed?
OlyKLin
OlyKLin
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 126
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
Evil Kraig F
Evil Kraig F
SSCrazy Eights
SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)

Group: General Forum Members
Points: 8675 Visits: 7660
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
OlyKLin
OlyKLin
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 126
Ok - Do I have to know the specific issues that I would be looking for in order to use that?
tmitchelar
tmitchelar
SSC Veteran
SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)

Group: General Forum Members
Points: 251 Visits: 508
My apologies, I replied to the wrong thread Sad 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.
OlyKLin
OlyKLin
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 126
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.
tmitchelar
tmitchelar
SSC Veteran
SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)

Group: General Forum Members
Points: 251 Visits: 508
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.
OlyKLin
OlyKLin
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 126
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.
ssisdiva
ssisdiva
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search