February 16, 2012 at 1:08 pm
Hey,
We get several flat files from our client we unzip and load into our databases. files may have number of records from 100k to more than 1 million.
Row delimiter is : ~|~~~||~~
Column delimiter is : ~|~
the error is only one record is sent to exception in each file. this occurs every time only in few files in which records will be > 100k. there are other files too which have million records in, but i didn't get any issue with it. same file may not have any exception for the next batch of files we get. the error is inconsistent to the files.
I even checked in the raw data, delimiters are correct.
i made this experiment too. i sent exception records to a flat file and loaded the same exceptions file with out modifying anything in it. then it loaded successfully.
Really i don't understand what type of error it could be. can anyone faced this type if issue with flat files?
Error got:
"Data conversion failed. The data conversion for column "Column 10" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page."
this is the sample row in the flat file:
XXXXX~|~123456~|~123456~|~123446~|~0~|~~|~~|~~|~~|~~|~~|~~|~~~||~~
February 16, 2012 at 1:42 pm
It means that the data in one of the columns is too long for the target table:
Run each statement separately.
CREATE TABLE #temp (integer int, characters varchar(5))
INSERT INTO #temp
SELECT 1, 'jared' --works
INSERT INTO #temp
SELECT 2147483648, 'jared' --fails
INSERT INTO #temp
SELECT 1, 'margaret' --fails
Jared
CE - Microsoft
February 16, 2012 at 2:10 pm
Length is 255 characters in table and flat file connection manager as well. am getting error while reading that record from flat file. As i told in my post, i redirected failed record to another flat file and if i tried to load that file again, it executed successfully.
February 16, 2012 at 2:15 pm
Yes, but what I am saying is that the actual data in the flat file is NOT 255. It is longer.
Jared
CE - Microsoft
February 16, 2012 at 2:16 pm
i redirected failed record to another flat file and if i tried to load that file again, it executed successfully
February 16, 2012 at 11:17 pm
rkumar.n3 (2/16/2012)
i redirected failed record to another flat file and if i tried to load that file again, it executed successfully
Maybe the data got truncated while writing to the destination during this redirection.
This would explain the succesful second run.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy