当源具有超过2个小数时，SSIS如何将字符串(DT_STR)转换为货币(DT_CY) [英] SSIS how to convert string (DT_STR) to money (DT_CY) when source has more than 2 decimals

问题描述

我有一个值如 24.209991 的源平面文件，但它们需要以类型 money 的形式加载到 SQL Server.在 DTS(我从中转换)中，该值为 24.21.如何在 SSIS 中转换该字段?

I have a source flat file with values such as 24.209991, but they need to load to SQL Server as type money. In the DTS (which I am converting from), that value comes across as 24.21. How do I convert that field in SSIS?

现在，我只是将类型从 DT_STR 更改为 DT_CY，它给出了数据转换失败"的运行错误.列Col003"的数据转换返回状态值 2 和状态文本由于可能丢失数据，无法转换该值.".

Right now, I am just changing the type from DT_STR to DT_CY, and it gives a run error of 'Data conversion failed. The data conversion for column "Col003" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".'

我是否使用数据转换任务?然后呢?

Do I use a Data Conversion task? And then what?

我也尝试将源输出列设置为 DT_NUMERIC，然后将其转换为 DT_CY，结果相同.

I've also tried setting the source output column to DT_NUMERIC, and then convert that to DT_CY, with the same result.

我也尝试过使用派生列，将 DT_STR 字段 Col003 转换为 (DT_NUMERIC,10,2)Col003，然后将其转换为 (DT_CY)Col003_Numeric.这会出现转换错误.

I've also tried using Derived Columns, casting the DT_STR field Col003 to (DT_NUMERIC,10,2)Col003 and then casting that to (DT_CY)Col003_Numeric. That's getting a cast error.

推荐答案

平面文件默认所有字段为 DT_STR.使用编辑连接的高级选项将数字字段设置为浮点数 (DT_R4).然后，在 Flat File Source 的高级编辑中(在 Data Flow 选项卡上)，将该输出列设置为 money (DT_CY).

The flat file defaults to all fields being DT_STR. Use the Advanced option on editing the connection to have the numeric field as float (DT_R4). Then, in the advanced editing of the Flat File Source (on the Data Flow tab), set that output column to money (DT_CY).

然后，该字段将在没有任何额外转换的情况下进行转换.问题是将源文件定义保留为 DT_STR.

Then, the field will convert without any additional conversions. The issue was leaving the source file definition as DT_STR.

这篇关于当源具有超过2个小数时，SSIS如何将字符串(DT_STR)转换为货币(DT_CY)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！