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

Format Parameter Error

Hi all, i am a newbie to reporting services. Hope someone can help me with this date problem.

I have a parameter date which i declared as type string in the report manager. when i entered the date as eg) "31/12/2003" and clicked on the 'preview' tab in the report manager, it gives me error saying that 'Cast from string "31/12/2003" to type 'Date' is not valid.'

So i use the cultureinfo function to set the date to 'en-US' format.... something like the one below:

CStr( Format(DateTime.Parse( Parameters!strDateTo.Value , New System.Globalization.CultureInfo("en-US")), "dd-MMM-yyyy"))

This works perfectly in my preview tab. But when i deployed to the server and when viewed on IE, it gives me an error msg -> "#Error" instead. Why is that so?

I have also checked my pc regional settings . It's set to United States and the date format is also "dd/MM/yyyy". Have also checked the settings in the server and it's also set to the same format. But i am still getting this error.

By the way... i am using windows 2003 with SRS 2000 SP2.

Any help will be appreciated. Thks !

First, why did you create a string parameter and not a date parameter? Second, the interpretation of the input is based on the language specified by the browser, which is why you got something different in the designer vs. when you deployed. Finally, if you want to evaluate strings based on a specific locale, you can just set the locale of the report (report property) to "en-US". You don't need to use a CultureInfo object.