CODEWITHSUNDEEP
ssissql-server-2012return-valueexecute-sql-task
Ce Wu
Ce Wu

Reputation: 13

How to use 'Return Value' parameter in Execute SQL task

I know there are 3 types of parameter in 'Parameter mapping' - Input Parameter, Output Parameter and Return Parameter. I understand how to use Input and Output parameter. But when I try to set the parameter type as 'Return Parameter', it doesn't work. Below is my SQL Server stored procedure.

ALTER Procedure [dbo].[spRandomReturn]
As 
Begin 
Return Convert(int, rand() * 10)
End

In SSIS Execute SQL task, I have set

  1. connection type: OLE DB

  2. parameter mapping: variable name: User::@random (I set SSIS a User parameter in SSIS: random INT32), Direction: ReturnValue, Type: Numeric, Parameter Name: @random

  3. SQL statement:

     Declare @r int = @random EXEC @r = spRandomReturn

I created a return parameter in SSIS, but it doesn't work and throws error.

Upvotes: 1

Views: 9154

Answers (1)

billinkc
billinkc

Reputation: 61269

Since you're using OLE DB Connection Manager, you need to use the ? to indicate where parameters are.

Thus, your query becomes 

EXECUTE ? = [dbo].[spRandomReturn]

And within your parameter mapping, you'd have

enter image description here

Reproduction

Biml, the Business Intelligence Markup Language, describes the platform for business intelligence. Here, we're going to use it to describe the ETL. BIDS Helper, is a free add on for Visual Studio/BIDS/SSDT that addresses a host of shortcomings with it. Specifically, we're going to use the ability to transform a Biml file describing ETL into an SSIS package. This has the added benefit of providing you a mechanism for being able to generate exactly the solution I'm describing versus clicking through many tedious dialogue boxes.

You can see in the following bit of XML, I create a connection called CM_OLE and this points to localhost\dev2014 at tempdb. You would need to modify this to reflect your environment.

I create a package named so_28419264. This package contains 2 variables. One is Query which contains the first bit of code. The second is ReturnValue which we will use to capture the return value on the Mapping tab. I initialize this one to -1 as the provided stored procedure would never generate a negative value.

I add two Tasks, both Execute SQL Tasks. The second one does nothing, it simply serves as a point for me to put a breakpoint on. The first Execute SQL Task is where we invoke our Stored Procedure and assign the results into our variable

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <OleDbConnection Name="CM_OLE" ConnectionString="Data Source=localhost\dev2014;Initial Catalog=tempdb;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;" />
    </Connections>
    <Packages>
        <Package ConstraintMode="Linear" Name="so_28419264">
            <Variables>
                <Variable DataType="String" Name="Query">EXECUTE ? = [dbo].[spRandomReturn];</Variable>
                <Variable DataType="Int32" Name="ReturnValue">-1</Variable>
            </Variables>
            <Tasks>
                <ExecuteSQL ConnectionName="CM_OLE" Name="SQL Demonstrate Return Value">
                    <VariableInput VariableName="User.Query" />
                    <Parameters>
                        <Parameter DataType="Int32" VariableName="User.ReturnValue" Name="0" Direction="ReturnValue" />
                    </Parameters>
                </ExecuteSQL>

                <ExecuteSQL ConnectionName="CM_OLE" Name="Put Breakpoint on me">
                    <DirectInput>SELECT 1;</DirectInput>                    
                </ExecuteSQL>                
            </Tasks>
        </Package>
    </Packages>
</Biml>

Results

It works

enter image description here

Upvotes: 5

Related Questions