How do I get SSIS Data Flow to put '0.00' in a flat file?

Question

I have an SSIS package with a Data Flow that takes an ADO.NET data source (just a small table), executes a select * query, and outputs the query results to a flat file (I've also tried just pulling the whole table and not using a SQL select).

The problem is that the data source pulls a column that is a Money datatype, and if the value is not zero, it comes into the text flat file just fine (like '123.45'), but when the value is zero, it shows up in the destination flat file as '.00'. I need to know how to get the leading zero back into the flat file.

I've tried various datatypes for the output (in the Flat File Connection Manager), including currency and string, but this seems to have no effect.

I've tried a case statement in my select, like this:

  CASE WHEN columnValue = 0 THEN 
     '0.00' 
  ELSE 
      columnValue 
  END

(still results in '.00')

I've tried variations on that like this:

 CASE WHEN columnValue = 0 THEN
     convert(decimal(12,2), '0.00') 
 ELSE 
     convert(decimal(12,2), columnValue) 
 END

(Still results in '.00')

and:

 CASE WHEN columnValue = 0 THEN
     convert(money, '0.00') 
 ELSE 
     convert(money, columnValue) 
 END

(results in '.0000000000000000000')

This silly little issue is killin' me. Can anybody tell me how to get a zero Money datatype database value into a flat file as '0.00'?

Answer 1

I was having the exact same issue, and soo's answer worked for me. I sent my data into a derived column transform (in the Data Flow Transform toolbox). I added the derived column as a new column of data type Unicode String ([DT_WSTR]), and used the following expression:

Price < 1 ? "0" + (DT_WSTR,6)Price : (DT_WSTR,6)Price

I hope that helps!