SQL 在 SSIS 包中将字符串转换为十进制值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14162749/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Converting a string to a decimal value in an SSIS package
提问by user1949225
I have a flat file that I am importing into SQL Server 2008 using an SSIS package.
我有一个平面文件，我正在使用 SSIS 包将其导入 SQL Server 2008。
The file contains a field with a number, followed by a column with a negative sign when the preceding column value is negative.
该文件包含一个带有数字的字段，当前一列值为负时，后跟带有负号的列。
For instance:
例如：
Price Sign
----- -----
9212.00
29.01 -
268.00 -
453.02
I want to load this value into a decimal column on a table.
我想将此值加载到表上的十进制列中。
In order to accomplish this, I am creating a derived column in my Data Flow Task. The expression for the derived column is [Sign] + [Price]. I have (using the Advanced Editor) defined the derived column as type decimal [DT_DECIMAL].
为了实现这一点，我在我的数据流任务中创建了一个派生列。派生列的表达式为[Sign] + [Price]。我已（使用高级编辑器）将派生列定义为类型 decimal [DT_DECIMAL]。
Then I'm simply loading the data (along with the derived column) into my table.
然后我只是将数据（连同派生列）加载到我的表中。
Does this sound like a good way to handle this situation? Is there another method that I should consider instead? Any 'gotchas' with using this method?
这听起来像是处理这种情况的好方法吗？我应该考虑另一种方法吗？使用这种方法有什么“问题”吗？
Thanks.
谢谢。
采纳答案by bobs
Your approach seems to be the best option. The default data type for each column from the flat file is string. So, your [Sign] + [Price]expression is doing string concatenation. And, the data is implicitly converted to a decimal value when moved to your output column.
你的方法似乎是最好的选择。平面文件中每一列的默认数据类型是字符串。因此，您的[Sign] + [Price]表达式正在执行字符串连接。并且，当移动到输出列时，数据会隐式转换为十进制值。
One change that would improve readability is to explicitly cast the string value to a decimal. You can change your expression in the Derived Column component to this:
提高可读性的一项更改是将字符串值显式转换为小数。您可以将派生列组件中的表达式更改为：
(DT_DECIMAL, scale)[Sign] + [Price]
where scaleis a number that will match the data type for your output column.
wherescale是与输出列的数据类型匹配的数字。
With this change, the output from your Derived Column component will be the decimal data type.
通过此更改，派生列组件的输出将是十进制数据类型。
回答by Justin
Maybe this will not work...
也许这行不通……
- You need to convert
Signto string data type andPriceto numeric. - Then compare if
Signis"-", if is, multiple from-1.
- 您需要转换
Sign为字符串数据类型和Price数字。 - 然后比较 if
Signis"-"，if is ，multiple from-1。
Derived Column Code Example:
派生列代码示例：
(DT_WSTR, 1) [Sign]=="-"? [Price]*-1 : [Price]
