x

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, 2010 at 08:59 PM in Default

ScottEdwards2000 gravatar image

ScottEdwards2000
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, 2010 at 11:45 PM

Rob Farley gravatar image

Rob Farley
5.7k 16 18 20

+1 That is exactly how I've handled this problem in the past.
Mar 22, 2010 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, 2010 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, 2010 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, 2010 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, 2010 at 09:08 AM

aRookieBIdev gravatar image

aRookieBIdev
2.3k 50 56 61

This doesn't work - I already had that option selected. But thanks for the help anyways.
Mar 17, 2011 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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x939
x66

asked: Mar 21, 2010 at 08:59 PM

Seen: 3535 times

Last Updated: Mar 21, 2010 at 08:59 PM