December 13, 2010 at 1:33 pm
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!
December 13, 2010 at 4:01 pm
Is 64 bit Oracle provider installed?
December 13, 2010 at 4:20 pm
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
December 13, 2010 at 5:22 pm
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.
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
December 13, 2010 at 5:25 pm
Ok - Do I have to know the specific issues that I would be looking for in order to use that?
December 13, 2010 at 5:38 pm
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.
December 13, 2010 at 8:09 pm
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.
December 14, 2010 at 8:49 am
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.
December 14, 2010 at 11:49 am
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.
December 15, 2010 at 6:37 am
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.
December 15, 2010 at 9:34 am
For the example I'm using, there is only a couple rows that will have this error. I want these errors to be sent to another file for review.
How do I do that and keep the import process to continue on?
December 15, 2010 at 12:59 pm
Did you configure the error output to redirect the row? The default is to fail the component.
December 15, 2010 at 1:02 pm
Yes. I did it myself and it didn't work so I found a walk through example and that didn't work. So, I'm at a loss
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply