Formating numbers in SSIS

I have a Amount field which is declared as Decimal. the data for this will be somethin like this 0.152

output need it to be -

00000.1520

How can I do this?

You'll have to convert it a string and prepend/append the zeroes.

-Jamie

|||Thank You. I converted the field into string and prepended the zero's. but it was too many steps. i wish SSIS had some kind of format function available to do this.|||

Godai B wrote:

Thank You. I converted the field into string and prepended the zero's. but it was too many steps. i wish SSIS had some kind of format function available to do this.

Too many steps? Really?

"0000" + (DT_STR, 20, 1252) 0.1520

That doesn't seem like too many to me. Can you give me examples of format functions like you require?

-Jamie

|||

this field is derived from 2 columns in a sql server table and the datatype is decimal(18,3)

the output format needs to be 000000.0000 .

the input field can have a single digit or upto six digits before the decimal point. if it has a single digit then i need to prepend "00000", if there are 2 digits then i prepend "0000" and so on.

same with digits after the decimal point.

ex: Input -->10.120 Output --> 000010.1200

Input --> .1 Output --> 000000.1000

what i did was, used the findstring function in the derived column to find the decimal point position and then got the predecimal digits and postdecimal digits. then with the help of length function i prepended or appended the zero's and then finally concatenated the predecimal and postdecimal digits.

Hope u can help me with an easy way to do this.

|||

I would use the FINDSTRING function as you have done to get the whole and the mantissa. But instead of using LENGTH I would just do this:

RIGHT("000000" + [wholepart], 6) + "." + REVERSE(RIGHT(REVERSE([mantissapart] + "0000"), 4))

Yeah, maybe a format function would be good.

-Jamie