SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 Numeric data Rounding issue
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqlmyworld
Starting Member

Australia
38 Posts

Posted - 10/26/2011 :  21:33:58  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 10/27/2011 :  03:11:19  Show Profile  Reply with Quote
whats the data type its using for field in sql 2008?

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

Go to Top of Page

sqlmyworld
Starting Member

Australia
38 Posts

Posted - 10/27/2011 :  04:02:42  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/27/2011 :  04:14:44  Show Profile  Reply with Quote
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/

Go to Top of Page

sqlmyworld
Starting Member

Australia
38 Posts

Posted - 10/27/2011 :  19:35:33  Show Profile  Reply with Quote
the data type coming from text file
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/28/2011 :  05:34:23  Show Profile  Reply with Quote
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/

Go to Top of Page

sqlmyworld
Starting Member

Australia
38 Posts

Posted - 10/30/2011 :  19:41:56  Show Profile  Reply with Quote
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
Go to Top of Page

sqlmyworld
Starting Member

Australia
38 Posts

Posted - 10/30/2011 :  19:46:38  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/31/2011 :  04:31:47  Show Profile  Reply with Quote
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/

Go to Top of Page

sqlmyworld
Starting Member

Australia
38 Posts

Posted - 11/03/2011 :  20:09:21  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 11/04/2011 :  05:26:22  Show Profile  Reply with Quote
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/

Go to Top of Page

sqlmyworld
Starting Member

Australia
38 Posts

Posted - 11/06/2011 :  18:46:52  Show Profile  Reply with Quote
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
Go to Top of Page

sqlmyworld
Starting Member

Australia
38 Posts

Posted - 11/07/2011 :  17:31:34  Show Profile  Reply with Quote
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.
Go to Top of Page

eng.amq
Starting Member

Malaysia
1 Posts

Posted - 03/13/2012 :  01:47:26  Show Profile  Reply with Quote
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.
Go to Top of Page

sqlmyworld
Starting Member

Australia
38 Posts

Posted - 07/05/2012 :  23:55:53  Show Profile  Reply with Quote
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.
Go to Top of Page

wackoyacky
Starting Member

25 Posts

Posted - 07/09/2012 :  16:25:08  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000