February 16, 2009 at 10:59 pm
Hi-
I'm trying to load a flat file into a table in SQL Server. Everythings looks good expect for the below error. Source column is [DT_I4] and data type in destination column is INT. No design time errors.
[Flat File Source [1]] Error: Data conversion failed. The data conversion for column "Column 20" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
[Flat File Source [1]] Error: The "output column "Column 20" (95)" failed because error code 0xC0209084 occurred, and the error row disposition on "output column "Column 20" (95)" specifies failure on error. An error occurred on the specified object of the specified component.
[Flat File Source [1]] Error: An error occurred while processing file "C:\SSIS Extracts\f16wkscn.txt" on data row 1.
[DTS.Pipeline] Error: The PrimeOutput method on component "Flat File Source" (1) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
Can someone tell me where I'm goign wrong?
Thanks!
February 16, 2009 at 11:40 pm
Hello,
Did you check the data in the File to make sure that all the values in the source for Column 20 can be converted to an integer? (-2,147,483,648 to 2,147,483,647)
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
February 17, 2009 at 9:01 am
Hi John-
I did notice that the value in extract is like 00000140, 00001236 etc fixed length. The problem is data in extract is string but I have to configure the data type as DT_I4 in the connection created for this extract. If I change the data type in this connection to DT_STR, I get an error because it is not compatible with the destination column type (INT/DT_I4).
How I can overcome this?
thanks
February 17, 2009 at 9:44 am
You may to try a derived column task and cast that column to DT_I4.
if this also doesnt work and you need to create function thats do it for you in the derived column task.
February 17, 2009 at 10:19 am
Hi- I did,
1. Added a derived column and casted the type to DT_I4. Expression used is (DT_I4) [Column 20]. In this case the data type of column 20 in Flat file source was DT_I4 and got the same error at run time.
2. Changed the data type in Flat file source from DT_I4 to DT_STR, casted column 20 using a derived column transformation. This time there was a design time error as the destination column in SQL table was INT.
Any thoughts?
February 17, 2009 at 10:45 am
I may not be getting whats exactly happening .
as i knw .column 20 from the the flat file has to go as DT_I4 in the SS.which u werent able to do with data conversion task .
so now u added one derived column task after data conversion.is that it.
what you can do is..from flat file let it come as dt_wstr in data conversion task.n then add a derived column for this column that will ultimately load destination table.
February 17, 2009 at 11:30 am
You know...I might have misunderstood the problem earlier.
If you are reading the data from a Flat File Connection Manager, you should be able to configure the input column so the data is read in as an Integer and not as a string value. Then you should be able to export it directly to your database field without any other transforms.
"The value could not be converted because of a potential loss of data."
If you are getting a conversion error from the Flat File (or a Data Conversion Object), then the string value must not truly be a (DT_I4) 4-byte integer value in all of your imported rows. Try checking for letters (o's, if it is a manually entered field), decimal places or characters that indicate negative numbers (like '-'), etc.
Last but not least, if it is always in the last column of the last row read in from you Flat File Connector, ensure your flat file is getting the final delimiter picked up on import.
If you are using a 'Fixed Length' setup on your Flat File Source and the error is always on the last row, it might not recognize or just be missing the final end of row marker. Try using a 'Ragged Right' setup instead and ensure the last column's delimiter is set to '{CR}{LF}' to see if that clears up your problem.
February 17, 2009 at 12:50 pm
Hi David-
Thanks for your detailed inputs but no luck, same error.
[Flat File Source [1]] Error: Data conversion failed. The data conversion for column "Column 20" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
Flat file is pipe delimited with fixed width for column 20, so always the number string will be in this format (00009999). The error occurs in the very first record itself when it process this int column. I tried the other options you mentioned, still the same error.
February 17, 2009 at 6:14 pm
Hi David-
your approach worked. There was a structure mismatch between the source and destination columns.
thanks!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply