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'?

Align

I'd love to know the cause of this issue - I'm not really comfortable with the Derived Column hack. – Todo

Answer 1

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

Zwiebel

Okay, after a bit of wrestling (to convert a numeric to a string), that worked. Thanks Soo! – Align

Answer 2

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!

Sipple

Thanks user346389... the inclusion of the weird SSIS derived column expression syntax was helpful. – Align

Just care for negative numbers. -0.25 will convert to -.25 from numeric to DT_WSTR. You could use something similar to [Price] < 1 ? ([Price] >= 0 ? "0" + (DT_WSTR,18)[Price] : [Price] > -1 ? "-0" + (DT_WSTR,18)(-1 * [Price]) : (DT_WSTR,18)[Price]) : (DT_WSTR,18)[Price] – Honora

Answer 3

I used the advanced editor to change the column from double-precision float to decimal and then set the Scale to 2:

Todo

the zeroes in the scale section of the decimal are truncated with this approach – Honora

Answer 4

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..

Earpiercing

Answer 5

It is good to use derived column and need to check the condition as well
pricecheck <=0 ? "0" + (DT_WSTR,10)pricecheck : (DT_WSTR,10)pricecheck

or alternative way is to use vb script

Zhao

Answer 6

Ultimately what I ended up doing was using the FORMAT() function.

CAST(FORMAT(balance, '0000000000.0000') AS varchar(30)) AS "balance"

This does have some significant CPU performance impact (often at least an order of magnitude) due to the way SQL Server implements that function, but nothing worked easier, more correctly, or more consistently for me. I was working with less than 100,000 rows and the package executes no more than once an hour. Going from 100ms to 1000ms just wasn't a big deal in my situation.

The FORMAT() function returns an nvarchar(4000) by default, so I also cast it back to a varchar of appropriate size since my output file needed to be in Windows-1252 encoding. Transcoding text is much more obnoxious in SSIS than it has any right to be.

Charleton