如何将 DT_R8 存储在 SSIS 中的变量中 [英] How to store DT_R8 in a variable in SSIS

问题描述

我有一个 SSIS 包，用于从具有浮点数据类型的源表中读取数据.它在 SSIS 中显示为 DT_R8(在外部列下)，我想将值存储在 SSIS 包中的变量中，并且我使用的是 Double 数据类型，但它不起作用并引发此错误...

I have an SSIS package to read data from a source table which has float data type. It is shown as DT_R8 in SSIS (under External Columns), i want to store the value in a variable in SSIS package and i'm using Double datatype, but it does not work and throws this error...

分配给变量User::varDBVAL"的值的类型与当前变量类型不同

The type of the value being assigned to variable "User::varDBVAL" differs from the current variable type

有人可以提出一个可能的解决方案吗，我只能找到 SSIS 和 SQL Server 之间的数据类型映射，而不能找到 SSIS 上的数据类型.

Can someone please suggest a possible solution, I could only find the data type mapping between SSIS and SQL Server but not for data types on SSIS.

谢谢约翰

推荐答案

我能够很好地做到这一点.我生成了一个包含 2 个执行 sql 任务的包:一个是 POC Maverick 的方式，另一个是做我相信你正在做的事情.

I was able to do this just fine. I generated a package with 2 execute sql tasks: One to POC Maverick's way and one to do what I believe you are doing.

如您所见，创建了 4 个变量:Mav、Query、QueryMav 和 varDBVAL.Mav 是一个整数，其上附加了一个赋值为零的表达式.这两个查询尽可能简单 - 选择一个常量值以确保我们生成了正确的类型.

As you can see, 4 Variables created: Mav, Query, QueryMav and varDBVAL. Mav is an integer with an expression attached to it assigning a value of zero. The two queries are simple as can be - a select of a constant value to ensure we have the correct type generated.

我相信 Maverick 是说在变量上设置表达式允许您在任何地方覆盖该值.此屏幕截图展示的是，虽然它在第一个执行 SQL 任务触发时不会出错，但实际上并没有坚持覆盖 @[User::Maverick] 的值.任务为变量分配了 1 的值，但表达式会覆盖该值并提供 0

I believe Maverick is stating that setting the expression on a Variable allows you to overwrite that value wherever you wish. What this screenshot is demonstrating is that while it does not error out when the first Execute SQL Task fires, the overwrite of the value of @[User::Maverick] does not actually stick. The task assigned a value of 1 to the Variable but the expression overrides that value and supplies 0

浮点数/双精度数的执行 SQL 任务很简单.我根据源查询返回单行数据

The Execute SQL Task for a float/double is straight forward. I return a single row of data based on my source query

在变量选项卡中，我将第零列分配给我的变量 @[User::varDBVAL] 并且执行工作正常

In the Variable's tab, I assign the zeroth column to my variable @[User::varDBVAL] and execution works just fine

如果您安装了 BIDS Helper，如果您编辑连接字符串以指向有效的 SQL Server 实例，您应该能够生成上述包并验证将浮点/双精度值分配给 SSIS 变量.

If you have BIDS Helper installed, if you edit the connection string to point to a valid SQL Server instance, you should be able to generate the above package and verify that the assignment of a float/double value into an SSIS Variable.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <OleDbConnection Name="CM_OLE" ConnectionString="Data Source=localhost\dev2012;Initial Catalog=tempdb;Provider=SQLNCLI10.1;Integrated Security=SSPI;"></OleDbConnection>
    </Connections>
    <Packages>
        <Package Name="so_2176077" ConstraintMode="Linear">
            <Variables>
                <Variable DataType="Double" Name="varDBVAL">0</Variable>
                <Variable DataType="String" Name="Query">SELECT CAST(1.0123 AS float) AS dbVal </Variable>
                <Variable DataType="Int32" Name="Mav" EvaluateAsExpression="true">0</Variable>
                <Variable DataType="String" Name="QueryMav">SELECT 1 AS Mav</Variable>
            </Variables>
            <Tasks>
                <ExecuteSQL ConnectionName="CM_OLE" Name="SQL MavTest" ResultSet="SingleRow">
                    <VariableInput VariableName="User.QueryMav"></VariableInput>
                    <Results>
                        <Result VariableName="User.Mav" Name="0"></Result>
                    </Results>
                </ExecuteSQL>

                <ExecuteSQL ConnectionName="CM_OLE" Name="SQL AssignValue" ResultSet="SingleRow">
                    <VariableInput VariableName="User.Query"></VariableInput>
                    <Results>
                        <Result VariableName="User.varDBVAL" Name="0"></Result>
                    </Results>
                </ExecuteSQL>
            </Tasks>
        </Package>
    </Packages>
</Biml>

参考

• 集成服务数据类型
• SQL Server 数据类型优先级

无论我使用 2012 还是 2008，结果都是一样的.

The results are the same whether I used 2012 or 2008.

如果显式 CAST 有效，那么根本原因是您没有对浮点数进行操作.它可能是一个十进制/数字，可以将错误消息解释为与浮点数不同的类型.

If the explicit CAST worked, then the root cause was you weren't operating on a floating point number. It was probably a decimal/numeric which would explain the error message as a that is a different type than a float.

2012 年确定数据类型的方法是通过系统存储过程 sys.sp_describe_first_result_set 或表值函数 sys.dm_exec_describe_first_result_set.您可以在下面看到分配给带有小数位的数字的默认数据类型与显式强制类型转换之间的区别.

The 2012 way of determining your data type is through the system stored proc sys.sp_describe_first_result_set or the table valued function sys.dm_exec_describe_first_result_set. Below you can see the difference between the default data type assigned to a number with decimal places versus an explicit cast to type.

SELECT 
    T.name AS column_name
,   T.column_ordinal
,   T.system_type_name
FROM 
    sys.dm_exec_describe_first_result_set(N'SELECT 1.0123 AS Defaults, CAST(1.10123 AS float) AS Explicits;', NULL, NULL) AS T;

1.0123 的默认解释将是 numeric(5,4) 并且只有显式转换为浮点数据类型才能真正得到浮点数.

The default interpretation of 1.0123 is going to be as numeric(5,4) and only be explicitly casting to the floating point data type do I actually get a float.

在 2005/2008 中可能有一种优雅的方式，但我只是将查询转储到表中 SELECT ... INTO dbo.Temporary WHERE NULL = NULL 然后查看相应的元数据.

There might be a graceful way in 2005/2008 but I'd just dump the query into a table SELECT ... INTO dbo.Temporary WHERE NULL = NULL and then look at the corresponding metadata.

这篇关于如何将 DT_R8 存储在 SSIS 中的变量中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！