SSIS Execute SQL Stored Procedure output parameter

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.

Answer 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

Answer 2

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.

Answer 3

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