如何让 SSIS 数据流将“0.00"放入平面文件中? [英] How do I get SSIS Data Flow to put '0.00' in a flat file?

问题描述

我有一个带有数据流的 SSIS 包，它采用 ADO.NET 数据源(只是一个小表)，执行一个 select * 查询，并将查询结果输出到一个平面文件(我也试过只拉整个表，而不是使用 SQL 选择).

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

问题是数据源拉取一列是 Money 数据类型，如果值不为零，它进入文本平面文件就好了(如123.45")，但当值为零时，它在目标平面文件中显示为.00".我需要知道如何将前导零返回到平面文件中.

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.

我在我的选择中尝试了一个 case 语句，如下所示:

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

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

(仍然产生.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

(仍然是.00")

和:

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

(结果为.0000000000000000000")

(results in '.0000000000000000000')

这个愚蠢的小问题害死我了.谁能告诉我如何将零 Money 数据类型数据库值作为0.00"放入平面文件中?

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

推荐答案

您能否使用派生列来更改值的格式?你试过了吗?

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

这篇关于如何让 SSIS 数据流将“0.00"放入平面文件中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！