I'm using SQL Server 2008 w/SP2.
我正在使用SQL Server 2008 w / SP2。
I've got an incoming decimal(9,2) field incoming through my OLE DB transformation to my recordset destination transformation. And I see that when I click on the Input and Output properties tab of the destination Transformation, it's showing this particular field as type DT_NUMERIC for some reason. It's like it's reading it as something other than a decimal? I don't know..I'm not an SSIS guru.
我有一个输入的十进制(9,2)字段通过我的OLE DB转换到我的记录集目标转换。我看到当我单击目标转换的输入和输出属性选项卡时,由于某种原因,它将此特定字段显示为DT_NUMERIC类型。它就像是把它读成小数以外的东西?我不知道......我不是SSIS的大师。
So continuing on...the problem I have starts here with me trying to stuff the value into a variable for this decimal field. In a foreach loop, I have a variable to represent this decimal field so I can work with it.
所以继续......我的问题从这里开始,我试图将值填入此十进制字段的变量。在foreach循环中,我有一个变量来表示这个十进制字段,所以我可以使用它。
The first problem that I believe is pretty well known is SSIS variables do not have a decimal type. And from my own testing and what I've read out there, people are using type object for the variable to make SSIS "happy" with decimal values? It makes mine happy.
我认为第一个问题是众所周知的是SSIS变量没有十进制类型。从我自己的测试和我在那里读到的,人们正在使用变量的类型对象使SSIS“满意”十进制值?这让我高兴。
But, then in my foreach loop, I have a for loop. And inside that I'm using an E*xecute SQL Task transformation*. In it, I need to create a parameter mapping to my variable so I can work with that decimal field in my T-SQL call in here. So now I see a type decimal for the parameter and use it and set that to point to my variable.
但是,然后在我的foreach循环中,我有一个for循环。在里面,我正在使用E * xecute SQL Task转换*。在其中,我需要创建一个映射到我的变量的参数,所以我可以在这里的T-SQL调用中使用该十进制字段。所以现在我看到参数的类型十进制并使用它并将其设置为指向我的变量。
When I run SSIS and it hits my SQL call, I get this in my output window.:
当我运行SSIS并且它命中我的SQL调用时,我在输出窗口中得到了这个:
The type is not supported.DBTYPE_DECIMAL
So I am hitting a wall here. All I wanna do is work with a decimal!!!
所以我在这里打了一堵墙。我想做的就是使用十进制!
2 个解决方案
#1
3
I would try either converting the value to a double or a varchar in your data flow task rather than an object. Note that you may have to use a cast or convert statement in your execute sql task to change the type back to decimal in your transact sql statement.
我会尝试将值转换为数据流任务中的double或varchar而不是对象。请注意,您可能必须在execute sql任务中使用强制转换或转换语句,以在transact sql语句中将类型更改回十进制。
#2
0
I had faced similar issue, and for my solution I used decimal to Int converter.
我遇到过类似的问题,对于我的解决方案,我使用了十进制到Int转换器。
#1
3
I would try either converting the value to a double or a varchar in your data flow task rather than an object. Note that you may have to use a cast or convert statement in your execute sql task to change the type back to decimal in your transact sql statement.
我会尝试将值转换为数据流任务中的double或varchar而不是对象。请注意,您可能必须在execute sql任务中使用强制转换或转换语句,以在transact sql语句中将类型更改回十进制。
#2
0
I had faced similar issue, and for my solution I used decimal to Int converter.
我遇到过类似的问题,对于我的解决方案,我使用了十进制到Int转换器。