SSIS Execute SQL Stored Procedure output parameter type mismatch

Question

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

[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.

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

The SQLStatement property of the task is:

EXEC [spGetFileId] @zFileName, @Id

I have the following SSIS variables:

User::File (String)

User::FileId (Int)

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

Variable | Direction | Type | Parameter

User::File | Input | String | @zFileName

User::FileId | Output | Int32 | @Id

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

EDIT:

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.

EDIT2:

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

The mapping and naming of the parameters depends on the connection you are using. Could you please told us wich are you using (ADO, OLE DB, ADO.NET...) — Zelloss
– Zelloss, Commented Oct 24, 2013 at 14:38

Kyle Hale · Accepted Answer · 2013-10-24 15:16:06Z

5

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

EXEC [spGetFileId] @zFileName, @Id OUTPUT

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.

edited Oct 24, 2013 at 15:16

Kyle Hale

8,1301 gold badge40 silver badges59 bronze badges

answered Oct 24, 2013 at 14:49

Chris Pickford

9,0606 gold badges51 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Metaphor · Accepted Answer · 2013-10-24 14:33:14Z

2

Instead of using an output parameter, use a result set, set ResultSet to "Single row" and map the result to your variable:

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

enter image description here

answered Oct 24, 2013 at 14:33

Metaphor

6,42512 gold badges58 silver badges81 bronze badges

1 Comment

Chris Pickford Over a year ago

I don't dispute that this would probably work, but it would mean going against internal convention for our stored procedures. +1 for the workaround but I'm afraid I can't accept the answer.

brasofilo · Accepted Answer · 2013-12-01 19:28:13Z

1

Make sure you're using an OLE DB connection for your Execute SQL Task and read this article - http://www.bidn.com/blogs/BillBrannen/bidn-blog/612/stored-procedure-return-values-and-output-parameters-in-ssis

edited Dec 1, 2013 at 19:28

brasofilo

26.2k15 gold badges96 silver badges189 bronze badges

answered Dec 1, 2013 at 19:06

user2867794

613 bronze badges

Collectives™ on Stack Overflow

SSIS Execute SQL Stored Procedure output parameter type mismatch

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related