fixed point arithmetic for price calculations

I've got a price in euro as a string, which I can easily cast to a numeric SSIS data type e.g. R4, R8, DECIMAL, NUMERIC. And I've got the dollar/euro exchange rate stored in an SSIS variable of type DOUBLE, set to 1.28 for testing purposes. I want to multiply the two values and return the (dollar) result, rounded (not truncated) to 2 decimal places, as a string.

Here are some experiments I did in an SSIS expression editor:

(DT_WSTR, 10) (1.28 * 31.10) evaluates to "39.8080"

(DT_WSTR, 10) (1.28 * (DT_R8) "31.10") evaluates to "39.808"

(DT_WSTR, 10) (1.28 * (DT_DECIMAL, 0) "31.10") evaluates to "39.68"

(DT_WSTR, 10) (1.28 * (DT_DECIMAL, 1) "31.10") evaluates to "39.808"

(DT_WSTR, 10) (1.28 * (DT_DECIMAL, 2) "31.10") evaluates to "39.8080"

(DT_WSTR, 10) (1.28 * (DT_DECIMAL, 3) "31.10") evaluates to "39.80800"

Of course, what I really want is "39.81", so I went on:

(DT_WSTR, 10) ((DT_DECIMAL, 0) (1.28 * (DT_R8) "31.10")) evaluates to "39"

(DT_WSTR, 10) ((DT_DECIMAL, 1) (1.28 * (DT_R8) "31.10")) evaluates to "39.8"

This looks promising! But:

(DT_WSTR, 10) ((DT_DECIMAL, 2) (1.28 * (DT_R8) "31.10")) evaluates to "39.8"

(DT_WSTR, 10) ((DT_DECIMAL, 3) (1.28 * (DT_R8) "31.10")) evaluates to "39.808"

Argh... How does one get a floating point value rounded to 2 decimal places?

(DT_NUMERIC, 6,2)(1.28 * 31.10) ?|||

Phil Brammer wrote:

(DT_NUMERIC, 6,2)(1.28 * 31.10) ?

Hmm... That didn't seem to work either.|||Use the ROUND() function. That will work for you.

ROUND(1.28 * 31.1 ,2)|||

(DT_WSTR, 10) ROUND(1.28 * 31.10, 2) indeed evaluates to "39.8100", which can simply be truncated.

I didn't think to try ROUND(numeric_expression, length) because the help text in the Expression Builder says that it returns an integer (regardless of the length parameter).

Thanks!

|||

Kevin Rodgers wrote:

(DT_WSTR, 10) ROUND(1.28 * 31.10, 2) indeed evaluates to "39.8100", which can simply be truncated.

Hmmm. I had to increase the string length from 10 to avoid a truncation error, so I changed it to 4000 which I think is the maximum for Unicode strings -- no worries. But more testing reveals that (DT_WSTR, 4000) ROUND(numeric_expression, 2) sometimes returns a value with fewer than 2 digits after the decimal point e.g. "16" instead of "16.00" or "133.5" insead of "133.50"'.

Here's what I'm using to ensure that there is a decimal point followed by 2 digits in the result:

FINDSTRING(usd_price, ".", 1) > 0 ? SUBSTRING(usd_price + "00", 1, FINDSTRING(usd_price, ".", 1) + 2) : usd_price + ".00"

firewalls? port numbers? ancient curses cast upon my servers?

Hello All,
Hopefully someone has come across this...

I have the client side of my application installed in the US,
with the application and the database servers running in london.

The program language is C#, all built in .net, with the .net installer.
When the user in the US runs the program and it gets to the
import progress part (background processing occurring on London servers),
they are getting no feedback as to the progress of the import.
ie, nothing is being sent to the US from the london server.
But everything runs smooth the other way, ie, tables are written to
the sql db's in london etc... And I get data sending and receiving
here in london when i run the client app on my machine and send to
the servers here, so its some problem with the connection with the US....
firewalls? port numbers? ancient curses cast upon my servers?

Cheers mike

Is this using SQL Server Integration Services (the SQL Server 2005 replacement for DTS)?
(In any case, if the "import" part isn't giving feedback, you probably should investigate the "import" part, to see what it does, and revise it to give feedback -- it may be hard for anyone here to know what this "import" part is -- if you suspect a connection problem, network sniffing, or even simple use of sysinternals tcpview may be helpful.)

firewalls? port numbers? ancient curses cast upon my servers?

Hello All
I have the client side of my application installed in the US, with the
application and the database servers running in london.
The program language is C#, all built in .net, with the .net installer. When
the user in the US runs the program and it gets to the import progress part
(background processing occurring on London servers), they are getting no
feedback as to the progress of the import. ie, nothing is being sent to the
US from the london server. But everything runs smooth the other way, ie,
tables are written to the sql db's in london etc... And I get data sending
and receiving here in london when i run the client app on my machine and send
to the servers here, so its some problem with the connection with the US...
firewalls' port numbers' ancient curses cast upon my servers' anyone come
across anything like this?
Cheers mikeHave you tested access with Query Analyzer to see if you can connect, access
the database from both places and perform the import tasks? It's unclear
from your post whether you're just having problems with the import task or
if it's even connecting.
joe.
"blomm via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:54B0800E732D8@.SQLMonster.com...
> Hello All
> I have the client side of my application installed in the US, with the
> application and the database servers running in london.
> The program language is C#, all built in .net, with the .net installer.
> When
> the user in the US runs the program and it gets to the import progress
> part
> (background processing occurring on London servers), they are getting no
> feedback as to the progress of the import. ie, nothing is being sent to
> the
> US from the london server. But everything runs smooth the other way, ie,
> tables are written to the sql db's in london etc... And I get data
> sending
> and receiving here in london when i run the client app on my machine and
> send
> to the servers here, so its some problem with the connection with the
> US...
> firewalls' port numbers' ancient curses cast upon my servers' anyone
> come
> across anything like this?
> Cheers mike|||"It's unclear
from your post whether you're just having problems with the import task or
if it's even connecting."
Sorry, i will try and clarify:
it is connecting, theres problems with the callback functionality i think, if
that means anything to you.
okay, thanks for your pointers, i'm off to follow them up.
m