Why is SSIS choking on Comma separators in numbers?

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

more ▼

asked Mar 21 '10 at 08:59 PM in Default

ScottEdwards2000 gravatar image

56 2 2 3

(comments are locked)
10|1200 characters needed characters left

2 answers: sort newest

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.

more ▼

answered Mar 21 '10 at 11:45 PM

Rob Farley gravatar image

Rob Farley
5.7k 15 18 20

+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)
10|1200 characters needed characters left

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
more ▼

answered Oct 22 '10 at 09:08 AM

aRookieBIdev gravatar image

2.3k 42 54 58

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)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Mar 21 '10 at 08:59 PM

Seen: 3230 times

Last Updated: Mar 21 '10 at 08:59 PM