I need some help with an error mystery.
This issue has now stumped two people and after nearly 11 hours on it I'm ready to throw up my hands. I'm not doing anything more complicated than pulling data and writing it to a flat file. Every time I run the extract I get:
[Flat File Destination [275]] Error: Data conversion failed. The data conversion for column "Column 0" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
There is only one column that could possibly be truncating and I've got the source set to ignore truncation. There is a source, a row counter, and the flat file destination in the data flow. The process bombs on the flat file. We've verified that we can
pull back all the data with no issue. Everything is mapped properly and all columns defined in the source fit their columns in the destination. The destination is a flat ragged right file with a CRLF marking rows. The process will write 430 some rows out of
some 30K before it blows up. When we knock down the record set to like 1000 rows the process will run fine.
At this point where sure the problem is somewhere in the data but I have no idea what it could possibly be or how to find it.
October 25th, 2012 12:46pm
The error looks to be coming from a Flat File Destination. I would suggest that the problem is that your source allows more characters than your Flat File Destination will allow. Try sending the errors and truncations for the Flat File Destination
to another file and see if you can find out the problem.Russel Loski, MCT, MCITP Business Intelligence Developer and Database Developer 2008 Blog: http://www.bidn.com/blogs/RussLoski/ Twitter: @sqlmovers
Free Windows Admin Tool Kit Click here and download it now
October 25th, 2012 1:08pm
It turns out in the connection manager you have to set the input column width and the output column width equal. I have NO idea why sometimes that's an issue and other times you can leave the input column field blank.
October 25th, 2012 1:37pm
That's what I tried to do this morning but it's a destination right? There was no option to redirect errors on the flat file elsewhere. There was an option to redirect the errors out of the source but since it's the flat file that's blowing up nothing
gets redirected.
Shows how much I know. Sorry.
What I would do is to check the metadata for the input to the destination. Which column feeds into "Column 0"? How many characters can it have? Then check the Advanced tab of the flat file destination connection manager. How many
characters can "Column 0" hold? If the input can hold more than the number of characters that your destination can contain, you will need to fix the problem (derived column comes to mind; it is isn't simple but you want to put the minimum of the length
of the column and the length of the destination column).Russel Loski, MCT, MCITP Business Intelligence Developer and Database Developer 2008 Blog: http://www.bidn.com/blogs/RussLoski/ Twitter: @sqlmovers
Free Windows Admin Tool Kit Click here and download it now
October 25th, 2012 1:39pm
Sorry we figured it out probably while you were typing this but yeah it was pretty much what your saying. It seems that 9 times out of ten I can go in connection manager in leave InputColumnWidth blank. This wasn't one of those times.
October 25th, 2012 1:43pm