August 12, 2008 at 7:04 am
Im am importing a flat file into a table. The flat file is delimited by comma and there is a field to be converted to double precision (DT_R8) from an ASCII file (DT_STR). In the package I use "Data Conversion" with types mentioned. It works fine provided the there is no decimal point in the string. In that case thepoint is ignored.
Example: .., -1582.61, ...
In the input file gives the value -158261.0 in the table
The National setting is Sweden and the code page is 1252.
What is wrong?
/Ö
August 12, 2008 at 8:30 am
one thing to look at is to put a data viewer from the ole db source to the data conversion and ensure that your string value with the decimal point in the file is exactly coming across as expected.
also, place a data viewer on the data conversion to the destination (or next step, however your data flow is configured) and ensure that the data is expected in there as well.
i was able to import a text file and create data conversion column with your parameters with no problem so i suspect that something else is going on.
August 13, 2008 at 1:19 am
I put a Data Viewer on both side of the conversion:
Flat File Source->
Viewer -> [1582.61]
Data Conversion ->
Viewer - [1582.61]
OLE DB Destination (Table with a double precision field)
[158261]
The field value is shown in [] parentheses. The final double precision value has ignored the decimal point!
The code page is 1252 at all places I have observed. It seems the decimal point is regarded as a fomatting character only?
/Örjan
August 13, 2008 at 1:47 am
I cannot recreate it. Everything as you have it, but for the source (I used script component) and the destination is correct.
Are your mappings for the destination correct?
What does profiler show you is being sent to SQL?
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
August 13, 2008 at 1:57 am
I have now changed the flat file to have text delimiter to allow for 'comma' instead of 'point' as digital separator:
"xx", "1582.61","y" ... -- does not work
"xx", "1582,61","y" ... -- works with comma
So, it burnt down to something more trivial. But where do I change this. Control panel nation setting is set to decimal point. Some setting in SQL Server?
/Ö
August 13, 2008 at 4:31 am
It was regional settings... Problem solved. Thanks for help in introducing the Data Viewer, which led me finally in the right direction!
/Ö
August 13, 2008 at 4:34 am
For the benefit of others, could you let us know what it was?
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
August 13, 2008 at 5:22 am
In Control Panel/Regional Settings you specify the region/country and eventual changes. For my Swedish settings the number format was set to comma as decimal separator. I thougth it was a point but it wasn't. With comma the point in the flat file input was evidently regarded as a formatting separator only. When changing the regional setting to point it worked with point in flat file. Surprisingly it worked also with comma in this case.
/Ö
August 28, 2009 at 8:15 am
hi,
Thank you a lot for your help!!
because i have the same problem (reverse process: export data from table to flat file)
The amounts columns have comma as decimal separator the are converted to DT_STR to change ',' to '.' and the result fields into flat file are displayed within comma.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply