|
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
(comments are locked)
|
|
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. +1 That is exactly how I've handled this problem in the past.
Mar 22 '10 at 12:38 AM
Tom Staab
Thanks Tom and Rob! Weird how "consumer" apps like Excel and Access often have options that M$ "enterprise" apps like SQL don't (Access crosstab queries come to mind...). Oh, well, I guess I am stuck with your "string" idea -- there is no way to do this with Bulk Insert "format files" is there? -- as a SSIS newbie, could you give me a little guidance on how to approach stripping out the comma and converting to number? I know this isn't the place for detailed tutorials, but just some first steps maybe... Thanks guys - this site is awesome - when should I post here vs forums on SSCentral?
Mar 22 '10 at 04:04 AM
ScottEdwards2000
Choosing where to post is usually a difference of question versus discussion but it's fairly grey. But then, you really can't go wrong with either :)
Mar 22 '10 at 10:21 AM
Blackhawk-17
How about posting to both - is that frowned upon? I've yet to get a good answer to another question I've posted here, and I'm tempted to post to the forums. Is there an established protocol for this that people prefer here? Should I link to the page here? I'm a bit shy based on a traumatic experience years ago on Usenet where I was unaware of the horrors of cross-posting! ;-)
Apr 15 '10 at 06:07 PM
ScottEdwards2000
(comments are locked)
|
|
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. Thanks, Kannan This doesn't work - I already had that option selected. But thanks for the help anyways.
Mar 17 '11 at 05:33 PM
ScottEdwards2000
(comments are locked)
|
