SSIS Output to Flat File as 0.00

Question

In SSIS I have a MONEY column in my OLE DB source that is formatted as 0.00. I want to output this value to a flat file in exactly the same format. So far, I've found two solutions:

1. Convert this to a string in my source and output a string to the file. Since this is a flat file, it doesn't care HOW I got to 0.00. It just uses this as the value.

2. Use a derived column in the data flow to generate a string with 0.00. This is just a harder way to achieve the first solution, but it can work in situations where I can't control the format of the source data.

I don't really like either of these solutions since it requires some extra work to force the format to a string properly. I can get the data to output to .00 if I change the data flow source output column to Numeric(18,2), but that doesn't completely solve the problem. Is there a way to output 0.00 without converting the data into a string?

Answer 1

Use the ABS() regular expression. It remains numeric and produces the "0.00" you want.

ABS(numeric_expression)

** look at the mathematical regular expressions. ABS may not be what you want, if you have negative values in your source data.