Help, "The value could not be converted because of potential loss of data"

Hi,

I am getting the error below in my Flat File Source.

I've seen this error many times before, and have successfully resolved this problem in the past.

However, this time it's a little different. It's complaining about row 7 of myFile.csv, column 20. I have column 20 defined as a Numeric(18,6). It also maps to the Price field in the table, which is also a Numeric(18,6).

The problem is, on row 7 of myFile, column 20 is blank. That is, there's no data for row 7, column 20.

So, why should it care about this If it's blank, then how can you lose any data I have several other blank columns in this file, but they aren't throwing any errors. Just this one.

Thanks

Errors:

[Flat File Source - myFile [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 - myFile [1]] Error: The "output column "Price" (333)" failed because error code 0xC0209084 occurred, and the error row disposition on "output column "Price" (333)" specifies failure on error. An error occurred on the specified object of the specified component.

[Flat File Source - myFile [1]] Error: An error occurred while processing file "d:\myDir\myFile.CSV" on data row 7.

This is what part of the data for row 7 looks like:

0.000, , , , , , ,

col 19 = 0.000

col 20 is the one after that one

Is this a normal problem with numbers being missing in flat file I have not encountered this before.

Would you recommend a script for the conversion

Thanks

Hi Subash,

I'm not exactly sure what you're recommending here

Are you suggesting this as a workaround Or just a way to make the "laborious" easier

Thanks

I find it rather unsettling to just now discover that if a flat file is missing a numeric value this will cause an error!

I find it unsettling because I have set up several packages where they read financial data extracts, which are all about numbers! (fortunately nothing is in production yet)

So far, I had not run into this problem until now. Does this mean I should err on the side of paranoia and read ALL numbers from flat files as STRINGS first, and make sure they're numbers Then convert to the proper data type

Any advice on best practices in this situation greatly appreciated!!!

When you say "DFT" do you mean a script that checks each value individually

I would be curious to see an example of this because I don't know what you mean exactly.

Thanks