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

Could you use a Derived Column to change the format of the value? Did you try that?

Answer 2

Hello, 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!

Answer 3

Since you are exporting to text file, just export data preformatted.

You can do it in the query or create a derived column, whatever you are more comfortable with.

I chose to make the column 15 characters wide. If you import into a system that expects numbers those zeros should be ignored...so why not just standardize the field length?

A simple solution in SQL is as follows:

    select 
    cast(0.00 as money) as col1
    ,cast(0.00 as numeric(18,2)) as col2 
    ,right('000000000000000' + cast( 0.00 as varchar(10)), 15) as col3
    go

 col1                  col2                 col3
 --------------------- -------------------- ---------------
                 .0000                  .00 000000000000.00

Simply replace '0.00' with your column name and don't forget to add the FROM table_name, etc..