October 7, 2013 at 10:05 am
Hello All,
I am having an issue with a derived column calculation in SSIS not returning the correct values. To give some background, I had to change the format of my calculation due to an overflow issue (resolved), see here - http://stackoverflow.com/questions/19223921/ssis-derived-column-calculation-error
Now, the old and new calculations return different values in SSIS, even though they are both mathematically the same and return the same value when executed in .net, excel and SQL.
In .Net -
decimal test1 = Convert.ToDecimal((415 * 415 * 415) * 0.000000030940000);
decimal test2 = Convert.ToDecimal((0.000000030940000 * 415 * 415 * 415));
both return 2.2113862225
In SSIS -
test1 = ((415 * 415 * 415) * 0.000000030940000)
test2 = (0.000000030940000 * 415 * 415 * 415)
return different values (test1 = 2.2113862225 and test2 = 2.211535)
Interestingly, SSIS sees test1 as a numeric (38,6) and test2 as a numeric (27,15)....
Has anyone encountered this issue before?
I don't really want to have to re-factor the whole package to use .net calculations, however that will be my last resort solution.
October 7, 2013 at 1:44 pm
Basically, looks like SSIS is trying to guess at the precision and scale needed, and when you multiply the smallest number first it is doing some rounding.
If you know the max precision and scale possible you can get around that by casting the output of each multiplication.
for example :
(dt_numeric,27,15)((dt_numeric,27,15)((dt_numeric,27,15)(0.000000030940000 * 415) * 415) * 415)
Also, you can get around the original problem by casting one of the 4206's to a bigint (DT_I8) value, I think.
The important part of knowing which if any of these will work is knowing how big any of these numbers will get and how much precision is sufficient
October 8, 2013 at 3:19 am
Hi, thanks for the response, but that didnt resolve the issue.
Touching on the last point in your email about converting to a bit int, I'll give that a go next, would prefer to go back to the old calculation that I know works.
October 8, 2013 at 3:44 am
Tried using the big int as suggested for the original calculation, that didnt work either, figures were different again.
In case someone else has this problem and needs a workaround -
I ended up using an OLE DB Command which calls a stored procedure (taking the calculation variables in and passing a result back as an output column).
Lesson of the day, do not attempt any complex calculations in a derived column. 🙂
Thanks for the help anyway.
Simon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply