SSIS 执行 SQL 存储过程输出参数类型不匹配 [英] SSIS Execute SQL Stored Procedure output parameter type mismatch

问题描述

我在 SSIS 包上收到以下错误:

I'm getting the following error on an SSIS package:

[执行 SQL 任务] 错误:执行查询EXEC [spGetFileId] @zFileName，@Id"失败并出现以下错误:分配给变量User::FileId"的值的类型与当前不同变量类型.变量在执行过程中不能改变类型.变量类型是严格的，对象类型的变量除外.

[Execute SQL Task] Error: Executing the query "EXEC [spGetFileId] @zFileName, @Id" failed with the following error: "The type of the value being assigned to variable "User::FileId" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

我创建了一个 执行 SQL 任务，它在 SQL 服务器上执行一个简单的存储过程:

I have created an Execute SQL Task which executes a simple stored procedure on the SQL server:

CREATE PROCEDURE [dbo].[spGetFileId]
    @zFileName VARCHAR(255),
    @Id INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT  @Id = [Id]
    FROM    [tblFileLog]
    WHERE   [zFileName] = @zFileName
END

任务的SQLStatement属性是:

EXEC [spGetFileId] @zFileName, @Id

我有以下 SSIS 变量:

• 用户::文件(字符串)
• User::FileId (Int)

• User::File (String)
• User::FileId (Int)

并且参数映射到SQL任务如下:

and the parameters are mapped on the SQL task as follows:

• 变量 |方向 |类型 |参数
• 用户::文件 |输入 |字符串 |@zFileName
• 用户::文件 ID |输出 |整数32 |@Id

• Variable | Direction | Type | Parameter
• User::File | Input | String | @zFileName
• User::FileId | Output | Int32 | @Id

我尝试使用变量类型(int16、int32 和 int64；甚至是字符串)，但错误仍然存​​在.请帮忙！

I've tried playing around with the variable types (int16, int32 and int64; even string) but the error persists. Please help!

调试存储过程我在 END 之前添加了一个 PRINT @Id 并且这输出了预期值但是在执行 SELECT @Id (输出参数)这将返回 NULL.

Debugging the stored procedure I added a PRINT @Id before the END and this outputs the expected value but when doing SELECT @Id (the output param) this returns NULL.

一位同事发现了问题并提供了他的解决方案，我在下面将其标记为答案.

A coworker has identified the problem and offered his solution which I have marked as answer below.

推荐答案

执行SQL任务上的SQLStatement必须在OUTPUT之后包含OUTPUT@Id 参数:

The SQLStatement on the Execute SQL Task must include OUTPUT after the @Id parameter:

EXEC [spGetFileId] @zFileName, @Id OUTPUT

并且您需要将 @User::FileID 参数设置为输出参数，以将变量启用为 readwrite 而不是 readonly 使其可以写入.

And you need to set your @User::FileID parameter an Output parameter to enable the variable as readwrite instead of readonly which lets it be written to.

这篇关于SSIS 执行 SQL 存储过程输出参数类型不匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！