Asked by:
Performing Division Operation and inserting into money datatype Expression
Question

hi all,
i have been struggling very hard for this with extensive searching and so on. I finally gave up and here i am posting the query.
I have a column known as CashChange from csv which i need to divide by hundred and insert into the table which is of money data type. This i am doing in the SSIS Derived transform.
Here is the expressions I have tried but to know avail. Could someone please tell me how to cast expression as decimal and then insert into db. Do i need ot use any data conversion Data flow task?
LEN(LTRIM(RTRIM(CashChange))) == (DT_DECIMAL,2)0 ? 0 : (DT_DECIMAL,2)((DT_I8)CashChange / 100)
LEN(LTRIM(RTRIM(CashChange))) == (DT_CY)0 ? 0 : (DT_CY)((DT_DECIMAL,2)CashChange / 100)
LEN(LTRIM(RTRIM(CashChange))) == (DT_DECIMAL,2)0 ? 0 : (DT_DECIMAL,2)((DT_CY)CashChange / 100)
With any of the above I am still ending up only 00. i.e. if the input is 12345 I am getting values as 12345.00 and not 123.45 which is expected.
Any help please?
Thursday, June 3, 2010 5:04 PM
All replies

The problem is that you're casting the result of your division to a decimal, but the calculation is being done using integer math. Try this:
(DT_DECIMAL, 2)((DT_I4)CashChange / 100.0)
The above will convert CashChange to an integer  which is what SSIS was implicitly doing for you (you must be using 2005, because I don't think 2008 will do that for you anymore). The division operation will result in a numeric or float with tons of decimal places, which you then explicitly convert to your required type.
(I'm not too sure why you're testing CashChange for zero  there isn't a potential for a division by zero error when you're specifying a divisor of 100.)
Talk to me now on
Thursday, June 3, 2010 5:22 PM 
I am still finding the same values coming. 12345.00 :( . we use ssis 2008.Thursday, June 3, 2010 5:36 PM


well as I said, The value for CashChange is coming from CSV as a whole number i.e. 12345 . The data type for the column is Money.Thursday, June 3, 2010 6:06 PM

Unfortunately, that can't be the case. There's no SSIS data type of "money". Do you mean the data type for the column is DT_CURRENCY? I am referring to the data type of the column "CashChange" as it exits the Flat File Source  you can doubleclick on the green arrow exiting the Flat File source to check this.
Talk to me now on
Thursday, June 3, 2010 6:14 PM 
Ok. Currently it is displayed as DT_STR. Well our requirement is to get the values in whole numbers and then divide by 100 and stored it as money in the database.
There are around 50 other columns based on teh same computation. I haven't yet modified this column type but is there no other way through which i can accomplish this task like conversion/type casting or something?
Thursday, June 3, 2010 6:45 PM 
just bumping the thread..anybody. please help :(Thursday, June 3, 2010 7:16 PM

Thursday, June 3, 2010 8:54 PM

Well, actually I am replacing the existing column. Should I be creating a new column then? I don't understand. Eitherways, should it not work? But thanks a lot , i will give it a try and let me see. Fingers fully crossed.
Friday, June 4, 2010 3:16 AM 