如何让SSIS数据流在平面文件中放置'0.00'？

Question

我有一个包含数据流的SSIS包，该数据流使用ADO.NET数据源（仅为小表），执行select *查询，并将查询结果输出到平面文件中（我也尝试了只拉取整个表而不使用SQL select）。

问题在于，如果数据源提取的列是Money数据类型，并且值不为零，则会正确地显示在文本平面文件中（如“123.45”），但当值为零时，它会在目标平面文件中显示为“.00”。我需要知道如何把前导零带回平面文件中。

我已经尝试了多种输出数据类型（在平面文件连接管理器中），包括货币和字符串，但似乎没有任何效果。

我尝试在我的select语句中使用了case语句，像这样：

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

(仍然会得到'.00'的结果)

我已经尝试过像这样的变体：

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

这个小问题真让我头疼。有人能告诉我如何将一个零金额的 Money 数据类型数据库值保存到一个平面文件中并显示为 '0.00' 吗？

- theog

---

我很想知道这个问题的原因 - 我对派生列hack并不是很熟悉。 - PeterX

Answer 1

我曾经遇到过完全相同的问题，然后看到了soo的答案。我将我的数据发送到了导出列转换器(在数据流转换工具箱中)。我添加了一个派生列作为新列，并将其数据类型设置为Unicode字符串([DT_WSTR])，然后使用以下表达式:

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

希望这可以帮助你!

- benjaminjsanders

---

感谢 user346389... 提供的帮助，其中奇怪的 SSIS 派生列表达式语法的包含非常有用。 - theog

只需关注负数。-0.25将从数字转换为DT_WSTR转换为-.25。您可以使用类似于[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]的内容。 - Luke

Answer 2

您可以使用“派生列”来更改值的格式吗？您尝试过这样做吗？

- soo

---

好的，在经过一番努力（将数字转换为字符串），成功了。谢谢Soo！ - theog

Answer 3

我使用高级编辑器将列从double-precision float更改为decimal，然后将Scale设置为2：

- PeterX

---

使用这种方法，小数的刻度部分中的零会被截断。 - Luke

Answer 4

由于您正在导出到文本文件，因此只需预先格式化导出的数据即可。

您可以在查询中执行此操作或创建派生列，以您更为舒适的方式进行操作。

我选择使列宽为15个字符。如果您将其导入到期望数字的系统中，则应忽略这些零...那么为什么不标准化字段长度呢？

在SQL中的一个简单解决方案如下：

    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

只需将“0.00”替换为您的列名，并别忘了添加FROM table_name等内容即可。

- john.da.costa

Answer 5

最终我所做的是使用FORMAT()函数。

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

这确实会对CPU性能产生一些显著的影响（通常至少一个数量级），因为SQL Server实现该函数的方式，但对我来说没有什么比它更容易、更正确、更一致的了。我处理的行数不到100,000行，而且该包每小时执行不超过一次。在我的情况下，从100毫秒到1000毫秒并不是什么大问题。

FORMAT()函数默认返回nvarchar(4000)，所以我还将其转换回适当大小的varchar，因为我的输出文件需要使用Windows-1252编码。在SSIS中进行文本转码比它应有的任何权利都要麻烦得多。

- Bacon Bits

Answer 6

使用派生列是很好的选择，同时需要检查条件
pricecheck <=0 ? "0" + (DT_WSTR,10)pricecheck : (DT_WSTR,10)pricecheck

或者另一种方法是使用VB脚本

- suresh