I am trying to import a tab-delimited text file using SSIS and it is choking on all the numeric fields because they numbers have comma-separators (e.g. 1,000.54). I keep getting this error:
The data conversion for column returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
This is happening with numeric fields and with integer fields. Is there no way to tell SSIS to ignore the comma? Seems like there would be, given the frequency of putting commas in numbers.
Please help - as I am having to open each file in Excel and highlight the columns and de-select the comma separator checkbox in the format dialog.
Thanks! Scott Edwards Pasadena, CA
asked Mar 21 '10 at 08:59 PM in Default
I don't know of an option for this, particularly as different cultures use commas and periods differently. For example, you often find "1 000,54" for the number you quoted earlier, or "1.000,54".
Trying pulling them into a string field, and then stripping the comma out and converting to number within the data flow.
answered Mar 21 '10 at 11:45 PM
In the text file source configuration select the columns as tab seperated instead of comma seperated.Also find and replace all the commas in the file.
answered Oct 22 '10 at 09:08 AM