Decimal rounding behavior differs between Prod and Dev (SSIS Package)

Question

I've a SSIS Package which calculates Rate and RateIns.

When I run this in DEV environment, Rate=1.3850588235, RateIns=1.1389411765

However, when I run this in Prod environment, Rate=1.3850588235, RateIns=1.1389411764

I tried to mimic SSIS logic in SSMS and code is below:

CREATE TABLE #TEMP(
GST [decimal](10, 2) NULL,
[GSTFin] [decimal](10, 2) NULL,
[GSTExl] [decimal](10, 2) NULL,
[Rate] [decimal](18, 10) NULL,
[RateIns] [decimal](18, 10) NULL
)
insert into #temp values (11773.00,2092.00,8500.00,0,0)
UPDATE #TEMP
SET Rate=ISNULL(GST,0)/ISNULL(GSTExl,0)
,RateIns=(ISNULL(GST,0) - ISNULL(GSTFin,0))/ISNULL(GSTExl,0) 

SELECT * FROM #temp
DROP TABLE #TEMP

Result: Both DEV & Prod show same values: Rate=1.3850588235, RateIns=1.1389411765

This confirmed that, issue is in SSIS package. I compared metadata of components in both Prod and Dev packages. All match. In fact, its the same SSIS package which has been deployed to Prod!

I wonder, why Prod SSIS package is showing RateIns=1.1389411764

Any suggestions?

Its not your package. It has something to do with decimal data type. Somebody had explained this earlier on SO, unfortunately I can't find the thread right now. — Prabhat G
Thank you Prabhat. Any hint to search it in google? I tried browsing on this topic, but of no avail. — Ash
Cheers ! I found it on SO again. : stackoverflow.com/questions/44918488/… — Prabhat G

Shawn S Shawn S · Accepted Answer · 2017-07-11T03:59:57

This might be a stupid question but is the hardware the same in prod versus dev. It's easy for different machines to give different results for floating point math. Maybe you could do that math based on IEEE 754 https://en.wikipedia.org/wiki/IEEE_754 rules and see if at least one environment is giving the correct results.

Here's an online ieee754 calculator also http://babbage.cs.qc.cuny.edu/IEEE-754/

Decimal rounding behavior differs between Prod and Dev (SSIS Package)

1 Answers