Execute SQL Task – Output Parameter on Stored Procedure causes task to fail.

Hot Topic

Issue installing SQL Server Express on Visual Studio Beta 2
SQL Server 2000 Reporting Services and Importing Access Reports
Using DSO for parallel partition processing
Failed to generate a user instance of SQL Server
Registry to store configuration values ?
Access denied
Total as the first column
Client Tools
Running a package in C#
Error attempting to import from Excel

SQL Server

DateDiff + Where am I going wrong?
SQL 2005 SP1 installation hangs
Grouping is weird, looks fine in Preview and the web but not in email.
SQL Server Authentication + SSL
Is this possible? How
same type of problem sql 2005 express install problems
error 193 installing .net framework 2.0
SQL Server 2000 Standard Edition Log Shipping
Scheduled updates with SQL Express
cannot install Northwind database in SQL Server Express

Execute SQL Task – Output Parameter on Stored Procedure causes task to fail.

< xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

I have a SQL Task that calls a stored procedure and returns an output parameter.  The task fails with error "Value does not fall within the expected range."

 

The Stored Procedure is defined as follows:

Create Procedure [dbo].[TestOutputParms]

            @InParm INT ,

            @OutParm INT OUTPUT

as

Set @OutParm  = @InParm + 5

 

The task uses an OLEDB connection and has a source type of Direct Input.  The SQL Statement is

Exec TestOutputParms 7, output

 

 The parameter mapping is:

Variable Name

Direction

Data Type

Parameter Name

User::OutParm

Output

LONG

@OutParm

 

The variable is defined as:

Name

Scope

Data Type

Value

OutParm

Execute SQL Task1

Int32

0

 

 

I traced the SQL statement in profiler and the RPC completed statement appears to set the output parameter correctly:

declare @p3 int

set @p3=12

exec sp_executesql N'Exec TestOutputParms 7, @P1 output',N'@P1 int OUTPUT',@p3 output

select @p3

 

 

From this I assume that the problem lies in the mapping of the parameter data type to the variable data type but I can’t see what is wrong.  I have also tried the same thing mapping a string variable to a varchar parameter but had the same result. What am I doing wrong

 

I am running the latest version of SQL 2005:

Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)

       Oct 14 2005 00:33:37

       Copyright (c) 1988-2005 Microsoft Corporation

       Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

Answer this question

Execute SQL Task – Output Parameter on Stored Procedure causes task to fail.

  • Honza

    For OLEDB Connection Types:

    1. You must select the OLEDB connection type.
    2. The IsQueryStoredProcedure option will be greyed out.
    3. Use the syntax EXEC = dbo.StoredProcedureName OUTPUT, OUTPUT, OUTPUT, OUTPUT The first will give the return code. You can use the syntax EXEC dbo.StoredProcedureName OUTPUT, OUTPUT, OUTPUT, OUTPUT to not capture the return code.
    4. Ensure a compatible data type is selected for each Parameter in the Parameters page.
    5. Set your parameters Direction to Output.
    6. Set the Parameter Name to the parameter marker's ordinal position. That is the first maps to Parameter Name 0. The second maps to Parameter Name 1, etc.

    For ADO.Net Connection Types:

    1. You must select the ADO.Net connection type.
    2. You must set IsQueryStoredProcedure to True.
    3. Put only the stored procedure's name in SQLStatement.
    4. Ensure the data type for each parameter in Parameter Mappings matches the data type you declared the variable as in your SSIS package.
    5. Set your parameters Direction to Output.
    6. Set the Parameter Name to the same name as the parameter is declared in stored procedure.
    I wrote about this on my blog

    How to use OUTPUT parameters with SSIS Execute SQL Task

  • Le Van Dung

    Can named parameters by used in this scenario What would be the name of the returncode parameter Could it be mapped to a package variable



  • DmVa

    I've got the named parameters working with ADO.Net (TDL!). The only thing left is to gifure out how to access the return value from the stored procedure with ADO.Net connections. Any ideas I'm still "Googling".



  • Fakrudeen Ali Ahmed

    Abercrombie07 wrote:
    Can named parameters by used in this scenario What would be the name of the returncode parameter Could it be mapped to a package variable

    You canot use named parameters with OLE DB connections, but you can with ADO.NET connections. Yes, you can map it to a package variable on the parameter mapping tab of the Execute SQL task. I don't know what the name would be.



  • Mark Berndt MCSD

    Hi,

    I am running into the same problem. I am using an OLEDB connection and have an input and output variable to the stored procedure. The input variable has a static value while the output variable is null.

    If I do not define the variables in the SQLStatement box, I get the error 'Variable must be defined'. If I do, I get the 'Value does not fall within the expected range.' error.

    I have marked my variables in the parameter mapping tab correctly as input and output and I am mapping String User variables to varchar parameters.

    Any suggestions on what I am doing wrong

    Thanks,

    Arun



  • Execute SQL Task – Output Parameter on Stored Procedure causes task to fail.
Answer this question