February 19, 2009 at 11:24 am
I hope someone has an explanation or a solution to this.
I have a query which pulls several smallmoney values via an OLE DB Source. If the value has zeros after the decimal point, it truncates them in the resulting metadata flow. IE, 75.00 becomes 75 and 50.80 becomes 50.8. This is seriously annoying because I need to keep those places after the decimal point for a file I'm sending to a mainframe.
I tried using Numeric in the output columns and Currency, neither worked. When I changed it to Decimal with a Scale of 2, I got 4 digits after the decimal which is way to many.
I figured I could deal with that, however, by using a LEFT() function with a LEN(MoneyAmt)-2 as my end point. Except for one problem. Apparently when I convert my DollarAmounts to string and use a REPLACE() function to get rid of the actual decimal point, I end up losing the trailing zeroes again.
ARGH.
To whit: 75.00 is supposed to end up as 7500 in a fixed width file. 50.80 is supposed to end up as 5080 and 82.33 is 8233 because the program at the other end knows that the last two columns are the "post decimal" columns.
And I can't get SSIS to cooperate with me, even though the query is showing me my correct values in SSMS.
Any thoughts?
February 19, 2009 at 11:29 am
Is there a reason you can't just multiply the original amount by 100?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 19, 2009 at 11:34 am
Hmm. Now that's something I hadn't thought about.
When I tried this idea, I ended up, yet again, with 2 extra zeros at the end, but I can get around that.
Thanks.
February 19, 2009 at 12:18 pm
WOW!
That was beyond complicated.
Here's what I did:
Changed the Output metadata column to decimal with a scale of 2. (Yes, I still had to do that).
Add 2 Derived Column transformations instead of 1.
First DC takes column "MyMoney" and creates a new DT_STR column ("MyMoney2") with a length of 13. I use the following formula: REPLACE((DT_STR,13,1252)(MyMoney * 100),".","")
This prevents the original two zeroes from disappearing and adds an additional two zeroes.
Second DC replaces MyMoney2 and uses the following formula:
REPLICATE("0",13 - LEN(REPLACE((DT_STR,13,1252)MyMoney 2,".",""))) + REVERSE(RIGHT(REVERSE(MyMoney 2),LEN(MyMoney 2) - 2))
The REVERSE/RIGHT/REVERSE part of the formula removes the two additional zeroes that I don't want. I have to do it this way because there's no LEFT function in SSIS. Or at least I can't find it.
The replicate part of the formula pads zeros in front of MyMoney2 up to a total length of 13 characters including the dollar amount total.
And finally, I have the values I want. 75.00 becomes 0000000007500, 50.80 becomes 0000000005080 and 82.33 becomes 0000000008233.
Whee! Wasn't that fun.
February 19, 2009 at 12:33 pm
DAMN!
It didn't completely work. Now it's padding the flat file with two SPACES after each dollar amount and truncating 2 zeros off the front of the fields.
GRRR.
EDIT: DOH! When I split my formula, I forgot to fix the REPLICATE part.
Update on Replicate is: REPLICATE("0",13 - (LEN(MyMoney2) - 2)) + ....
Now that's embarrassing. @=)
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply