Numeric data Rounding issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

Numeric data Rounding issue

Author

Topic

sqlmyworld
Starting Member

38 Posts

Posted - 2011-10-26 : 21:33:58

I have converted SQL 2000 DTS pkg to SQL 2008 SSIS pkg. While Bulk loading, SQL 2000 is not rounding numeric data type but in SQL 2008 it is rounding the last digit of the scale (Though, I provided proper precision and scale for numeric data type).

For example – in SQL 2000 the value is – 61.5151 but in SQL 2008 value became – 61.5152 while bulk loading.

Is there any way I can stop doing this rounding in SQL 2008? Do I have to configure some settings in SQL Server 2008 or set some property in Bulk insert task? So, instead of rounding numeric data it will Truncate up to 4 decimal point (scale).

Thanks in advance for your.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-27 : 03:11:19

whats the data type its using for field in sql 2008?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlmyworld
Starting Member

38 Posts

Posted - 2011-10-27 : 04:02:42

datatype Numeric(15,4) for both SQL 2000 and SQL 2008. Same datafiles are loaded into 2000 and 2008 and both provides different result as I mentioned above.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-27 : 04:14:44

quote:
Originally posted by sqlmyworld

datatype Numeric(15,4) for both SQL 2000 and SQL 2008. Same datafiles are loaded into 2000 and 2008 and both provides different result as I mentioned above.

and what about data type in source? is it coming from db table or file?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlmyworld
Starting Member

38 Posts

Posted - 2011-10-27 : 19:35:33

the data type coming from text file

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-28 : 05:34:23

quote:
Originally posted by sqlmyworld

the data type coming from text file

how data is present in file and whats type its assuming for input column from file?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlmyworld
Starting Member

38 Posts

Posted - 2011-10-30 : 19:41:56

Data presents in file in below format. Tab is used for column separator and Pipe is used for row separator during bulk insert.

During Bulk insert - table expected Numeric(15,4) data type for columns Current Value (curr_lv) and Original Value(orig_lv)

Data file --> |47674 305 126 165000.000000 15 October 2009 61.515152 165000.000000 15 October 2009 61.515152 101500.000000|

Data in Table -->
ID s_no m_no c_val c_val_date curr_lv orig_val orig_val_date orig_lv amt
IN SQL2000 47674 305 126 165000.00 2009-10-15 00:00:00 61.5152 165000.00 2009-10-15 00:00:00 61.5152 101500
IN SQL2000 47674 305 126 165000.00 2009-10-15 00:00:00 61.5152 165000.00 2009-10-15 00:00:00 61.5152 101500

sqlmyworld
Starting Member

38 Posts

Posted - 2011-10-30 : 19:46:38

Sorry, I mean the difference in sql 2000 & sql 2008 as below...

This is just one sample row from a flat file. I have lots of other flat files and tables which have same issues...

Flat Data file --> |47674 305 126 165000.000000 15 October 2009 61.515152 165000.000000 15 October 2009 61.515152 101500.000000|

Data in Table -->
ID s_no m_no c_val c_val_date curr_lv orig_val orig_val_date orig_lv amt
IN SQL2000 47674 305 126 165000.00 2009-10-15 00:00:00 61.5151 165000.00 2009-10-15 00:00:00 61.5151 101500
IN SQL2008 47674 305 126 165000.00 2009-10-15 00:00:00 61.5152 165000.00 2009-10-15 00:00:00 61.5152 101500

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-31 : 04:31:47

sorry the format is horrible. Cant make out which are column value

would you mind properly formatting it using code tags?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlmyworld
Starting Member

38 Posts

Posted - 2011-11-03 : 20:09:21

Visakh,

I formatted both data file and SQL table values using comma separated. But in flat file as I mentioned earlier, all columns are separated by TAB and all row separated by PIPE |
You can copy these values and open csv file into excel to get more clarity of below data. Hope this format helps to understand the issue.

values in Flat Data File --> 47674,305,126,165000,15 October 2009,61.515152,165000,15 October 2009,61.515152,101500

below values are from table after bulk insert process has been finished on SQL 2000 & 2008
SQL Version,ID,s_no,m_no,c_val,c_val_date,curr_lv,orig_val,orig_val_date,orig_lv,amt
IN SQL2000,47674,305,126,165000,2009-10-15 00:00:00,61.5151,165000,2009-10-15 00:00:00,61.5151,101500
IN SQL2008,47674,305,126,165000,2009-10-15 00:00:00,61.5152,165000,2009-10-15 00:00:00,61.5152,101500

Pl let me know if you have any futher questions.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-04 : 05:26:22

actually upto what precision you need store this in yourtable? also you want it to undergo truncation or rounding?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlmyworld
Starting Member

38 Posts

Posted - 2011-11-06 : 18:46:52

as I mentioned earlier, in table I want Numeric field with precision & scale value of (15,4). So, for both SQL 2000 & 2008 the column datatype is Numeric(15,4). but in Sql2000 it is truncating and sql2008 looks like it is rounding. do you have any idea why it is doing different while Bulk loading in 2000 & 2008?

For your info -- for example, the value of "orig_lv" from DATAfile is 61.515152 but in SQL 2000 it is stored as 61.5151 and SQL 2008 it is stored as 61.5152

sqlmyworld
Starting Member

38 Posts

Posted - 2011-11-07 : 17:31:34

Visakh,

As mentioned in my last post, I want to store numeric data upto 4 digit after decimal place and want to truncate (as SQL 2000 is truncating while bulk loading) data after four digit of decimal places.

eng.amq
Starting Member

1 Post

Posted - 2012-03-13 : 01:47:26

quote:
Originally posted by sqlmyworld

I have converted SQL 2000 DTS pkg to SQL 2008 SSIS pkg. While Bulk loading, SQL 2000 is not rounding numeric data type but in SQL 2008 it is rounding the last digit of the scale (Though, I provided proper precision and scale for numeric data type).

For example – in SQL 2000 the value is – 61.5151 but in SQL 2008 value became – 61.5152 while bulk loading.

Is there any way I can stop doing this rounding in SQL 2008? Do I have to configure some settings in SQL Server 2008 or set some property in Bulk insert task? So, instead of rounding numeric data it will Truncate up to 4 decimal point (scale).

Thanks in advance for your.

Hi sqlmyworld,

Did you find a solution for your problem? I also stumbled accross the same issue exactly. I want SQL 2008 to truncate rather than round when I bulk insert from a flat file. This is a very critical issue in a very critical system we're developing. Hope you share your solution if you ever found it, I will really appreciate it.

sqlmyworld
Starting Member

38 Posts

Posted - 2012-07-05 : 23:55:53

Sorry for late reply. I couldn't find solution to this issue but we had a workaround for this problem. We truncated the source value to four digit. For example, the value of "orig_lv" from DATAfile is 61.515152 so we trucate the source value to 61.5151 and then bulk loading to SQL 2008. Hope this help.

wackoyacky
Starting Member

25 Posts

Posted - 2012-07-09 : 16:25:08

Might be a little bit off topic, but I had a similar issue but the other way around. SSIS 2008 is truncating the decimal instead of rounding if I set numeric(18,2) in the Flat File Source Manager. Your issue is what exactly what I want :)

Right now the only solution that I can think of is read the data with the largest anticipated decimal places for that field and then use the ROUND function on each of the columns that needs to be rounded of before they are inserted to the destination database.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources