如何在 SSIS SQL 任务中使用标量值 tSQL 函数设置变量 [英] How to set a variable using a scalar-valued tSQL function in SSIS SQL Task

问题描述

我有一个 tSQL 标量值函数

I have a tSQL scalar-valued function

ALTER FUNCTION [dbo].[MyDB_GetJobId]
(
)
RETURNS [uniqueidentifier]

它的结果需要进入一个 SSIS 用户变量 vJobId，声明为 String.SqlStatementSource 是 EXEC 吗?= dbo.MyDB_GetJobId();结果集如下: Result Name: 0;变量名称:User::vJobId.

Its results need to go into a SSIS user variable vJobId, declared as String. SqlStatementSource is EXEC ? = dbo.MyDB_GetJobId(); result set is as follows: Result Name: 0; Variable Name: User::vJobId.

不行，报错信息如下

EXEC ? = dbo.MyDB_GetJobId()"失败并出现以下错误:没有为一个或多个必需参数提供值.".可能的失败原因:查询有问题，ResultSet"属性设置不正确，参数设置不正确，或者连接建立不正确.

连接正常(普通 SQL 请求运行正常)，没有输入参数，...你能帮忙吗?谢谢.

Connection is fine (a plain SQL request runs ok), no input parameters, ... Could you help? Thanks.

推荐答案

我最初尝试使用带有返回方向的参数映射，但那是错误的.

I initially went down the path of trying to use a Parameter Mapping with a direction of Return but that was incorrect.

相反，我按所示配置了我的执行 SQL 任务.我的 ResultSet 是一个单行".我的 SQLStatement 只是SELECT dbo.MyDB_GetJobID()"

Instead, I have my Execute SQL Task configured as shown. My ResultSet is a "Single Row". My SQLStatement is simply "SELECT dbo.MyDB_GetJobID()"

在结果集选项卡中，由于这是 Single Row 的 ResultSet 类型，因此我们为每列提供了一个基于零的序数系统的映射.

In the result set tab, since this is a ResultSet type of Single Row, then we provide a mapping per column with a zero based ordinal system.

这是一个演示将结果分配给变量 User::SingleRow 的示例.您可以忽略 Other，因为我试图通过参数映射"选项卡使用它.

This is an example demonstrating the result being assigned to the variable User::SingleRow. You can ignore Other as I was trying to make it work with via the Parameter Mapping tab.

这篇关于如何在 SSIS SQL 任务中使用标量值 tSQL 函数设置变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！