如何将 SQL 查询结果的数值赋给数据类型为 Double 的包变量?

发布于 2024-11-26 14:51:58 字数 1106 浏览 2 评论 0原文

我有一个简单的任务 - 使用执行 SQL 任务中的 ResultSet 填充用户变量

SELECT MainID FROM TableA WHERE TableA_ID = 1` 

(这只会返回一列和一行)。

MainID 当前作为用户定义的数据类型存储在数据库中。该值以 XXXX.Y 格式存储(例如 8008.1)。

如果我将 UserVariable 更改为字符串数据类型,则可以毫无问题地分配该值,无论是将结果集强制转换还是将结果集转换为字符串/数字/浮点数。

SELECT CAST(MainID as NUMERIC(9,1)) as 'MainID' FROM TableA WHERE TableA_ID = 1;
SELECT CAST(MainID as FLOAT) as 'MainID' FROM TableA WHERE TableA_ID = 1;
SELECT CAST(MainID as VARCHAR(10)) as 'MainID' FROM TableA WHERE TableA_ID = 1;

SELECT CONVERT(NUMERIC(9,1), MainID) as 'MainID' FROM TableA WHERE TableA_ID = 1;
SELECT CONVERT(FLOAT, MainID) as 'MainID' FROM TableA WHERE TableA_ID = 1;
SELECT CONVERT(VARCHAR(10), MainID) as 'MainID' FROM TableA WHERE TableA_ID = 1;

但是,如果用户变量数据类型为 DOUBLE,我无法分配该值。我收到以下错误消息:

[执行 SQL 任务] 错误:赋值时发生错误 变量“dUserVariable”:“分配给的值的类型 变量“User::dUserVariable”与当前变量类型不同。 变量在执行期间不能改变类型。变量类型有 严格,但 Object 类型的变量除外。 ”。

我对 SSIS 相当陌生,所以我对如何完成此转换感到有点困惑。

有什么想法吗?

I have what should be a simple task - Populate a user variable using the ResultSet from an Execute SQL task

SELECT MainID FROM TableA WHERE TableA_ID = 1` 

(This will only return one Column and one Row).

MainID is currently stored as a user-defined datatype in the database. The value is stored in the format XXXX.Y (e.g. 8008.1).

If I change the UserVariable to a String datatype, I can assign the value without any problems either CASTing or CONVERTing the ResultSet to STRING/NUMERIC/FLOAT.

SELECT CAST(MainID as NUMERIC(9,1)) as 'MainID' FROM TableA WHERE TableA_ID = 1;
SELECT CAST(MainID as FLOAT) as 'MainID' FROM TableA WHERE TableA_ID = 1;
SELECT CAST(MainID as VARCHAR(10)) as 'MainID' FROM TableA WHERE TableA_ID = 1;

or

SELECT CONVERT(NUMERIC(9,1), MainID) as 'MainID' FROM TableA WHERE TableA_ID = 1;
SELECT CONVERT(FLOAT, MainID) as 'MainID' FROM TableA WHERE TableA_ID = 1;
SELECT CONVERT(VARCHAR(10), MainID) as 'MainID' FROM TableA WHERE TableA_ID = 1;

However, I can't assign the value if the User Variable Datatype is DOUBLE. I get the following error message:

[Execute SQL Task] Error: An error occurred while assigning a value to
variable "dUserVariable": "The type of the value being assigned to
variable "User::dUserVariable" differs from the current variable type.
Variables may not change type during execution. Variable types are
strict, except for variables of type Object. ".

I'm fairly new to SSIS so I'm a bit stumped on how to get this conversion to go through.

Any ideas?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

开始看清了 2024-12-03 14:51:58

我不确定以下选项是否适用于 Web 服务任务,但您可以尝试一下。

我认为您所描述的问题是由于将数值分配给数据类型 Double 的 SSIS 包变量时 SSIS 的限制造成的。请参阅此链接 到已报告此问题的 MSDN Connect。

下面是一个简单的分步示例,说明了您可以尝试的选项。此示例是在 SSIS 2008 R2 中创建的。

创建 SSIS 包。我在开头以 YYYYMMDD_hhmm 的格式命名该包,后面跟着 SO 代表 Stack Overflow,然后是 SO 问题 id,最后是描述。这是为了我以后可以轻松地引用它。请参阅屏幕截图#1

在包上,创建两个变量,即 StringValueDoubleValue。将变量 StringValue 设置为数值(例如 0)。这是必要的,这样我们要在变量 DoubleValue 中配置的表达式就不会出错。选择变量 DouleValue 并按 F4 查看属性。将属性 EvaluateAsExpression 设置为 True 并将 Expression 属性设置为 (DT_DECIMAL, 2) @[User::StringValue] 注意: 此时变量 DoubleValue 上的数据类型将更改为 Cell 不是字符串。 请参阅屏幕截图 # 2 和#3

在包上,创建一个到某些 SQL Server 数据库的 OLE DB 连接管理器。我已经创建了与本地计算机上的 Adventure Works 数据库的连接。请参阅屏幕截图#4

在“控制流”选项卡上,放置一个“执行 SQL 任务”并对其进行配置,如屏幕截图 #5#6 中所示。双击执行 SQL 任务以打开执行 SQL 任务编辑器。将 ResultSet 设置为 SingleRow 因为我们将只获取一个值。将连接字符串设置为 OLE DB 连接管理器。 SQL 语句应设置为 SELECT CAST('3.14' AS NUMERIC(10,2)) AS ValueOfPi。结果集应设置为变量StringValue

在“控制流”选项卡上,将“脚本任务”放置在“执行 SQL 任务”之后。此脚本任务只是显示由于我们刚刚配置的表达式而将分配给 DoubleValue 变量的值。双击脚本任务以打开脚本任务编辑器。单击编辑脚本...按钮以打开 VSTA 编辑器。将 Main() 方法替换为脚本任务代码部分中给出的代码。请参阅屏幕截图 #7#8

执行包。变量 DoubleValue 中的值应显示在消息框中。请参阅屏幕截图#9

希望有帮助。

脚本任务代码:

C#代码,只能在SSIS 2008及更高版本中使用。

public void Main()
{
    Variables varCollection = null;
    Dts.VariableDispenser.LockForWrite("User::DoubleValue");
    Dts.VariableDispenser.GetVariables(ref varCollection);

    MessageBox.Show(varCollection["User::DoubleValue"].Value.ToString());

    Dts.TaskResult = (int)ScriptResults.Success;
}

屏幕截图:

#1:

1

#2:

2

#3:

3

#4:

4

#5: >

5

#6:

6

#7:

7

#8:

8

#9:

“9”

I am not sure if the following option will work in Web Service Task but you can give it a try.

I think that the issue you have described is due to a limitation in SSIS while assigning numeric value to an SSIS package variable of data type Double. Refer this link to MSDN Connect where this issue has been reported.

Here is a simple step-by-step example that illustrates an option that you can try. This example was created in SSIS 2008 R2.

Create an SSIS package. I have named the package in the format YYYYMMDD_hhmm in the beginning followed by SO stands for Stack Overflow, followed by the SO question id, and finally a description. This is for me to easily refer this back later. Refer screenshot #1.

On the package, create two variables namely StringValue and DoubleValue. Set the variable StringValue to numeric value (say 0). This is necessary so that the expression we are going to configure in the variable DoubleValue doesn't error out. Select the variable DouleValue and press F4 to view the properties. Set the property EvaluateAsExpression to True and set the Expression property to (DT_DECIMAL, 2) @[User::StringValue] NOTE: At this point the Data Type on the variable DoubleValue will change to Cell is not a string. Refer screenshots #2 and #3.

On the package, create an OLE DB connection manager to some SQL Server database. I have created a connection to Adventure Works database that I have on my local machine. Refer screenshot #4.

On the Control Flow tab, place an Execute SQL Task and configure it as shown in screenshots #5 and #6. Double-click on the Execute SQL Task to bring the Execute SQL Task Editor. Set the ResultSet to SingleRow because we will be fetching only one value. Set the connection string to the OLE DB connection manager. The SQL Statement should be set to SELECT CAST('3.14' AS NUMERIC(10,2)) AS ValueOfPi. Result Set should be set to the variable StringValue.

On the Control Flow tab, place a Script Task after the Execute SQL Task. This script task is just to show the value that will be assigned to the DoubleValue variable due to the expression that we just configured. Double-click on the Script Task to bring the Script Task Editor. Click on the Edit Script... button to bring the VSTA editor. Replace the Main() method with code given under Script task code section. Refer screenshots #7 and #8.

Execute the package. The value in the variable DoubleValue should be displayed in a message box. Refer screenshot #9.

Hope that helps.

Script task code:

C# code that can be used only in SSIS 2008 and above.

public void Main()
{
    Variables varCollection = null;
    Dts.VariableDispenser.LockForWrite("User::DoubleValue");
    Dts.VariableDispenser.GetVariables(ref varCollection);

    MessageBox.Show(varCollection["User::DoubleValue"].Value.ToString());

    Dts.TaskResult = (int)ScriptResults.Success;
}

Screenshots:

#1:

1

#2:

2

#3:

3

#4:

4

#5:

5

#6:

6

#7:

7

#8:

8

#9:

9

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文