The execution of a SP in SSIS returns nothing

Question

Until now I've been looking for a possible solution to the execution of a sp from SSIS, but anything seems to work. I´ve got a sp:

CREATE PROCEDURE [DBO].[SPIDENTIFIERS] @IDENT NVARCHAR(MAX) OUTPUT

What I need is to save the result in a variable that I've created in SSIS.

This is the configuration that I used to try to do it.

In the parameter set section I have also used the Direction as Output or ReturnValue but I received a error message. Just to try I put a Script Task to chek the value, but as you can see this is empty.

With the Direction Ouput or ReturnValue I've got this:

[Execute SQL Task] Error: Executing the query "EXECUTE  spIdentifiers ? OUTPUT;" failed with the following error: 
    "El valor no está dentro del intervalo esperado.". 
    Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, 
    parameters not set correctly, or connection not established correctly.

What am I missing in the configuration of the task?.

I looked for an answer in this post. But nothing seems to work

How do you call a Stored Procedure in SSIS?

SSIS Stored Procedure Call

Thanks in advance.

Answer 1

Your parameter should not be named, as @gerald Davis has indicated. For a connection manager of OLEDB type, it should be ordinal based, thus 0

Here's my sample package and you can see that my variable @[User::MyVariables] is populated with a lot of Xs

Here's my proc definition

IF NOT EXISTS
(
    SELECT
        *
    FROM
        sys.procedures AS P
    WHERE
        P.name = N'SPIDENTIFIERS'
)
BEGIN
    EXECUTE sys.sp_executesql N'CREATE PROC dbo.spidentifiers AS SELECT ''stub version, to be replaced''';
END
GO
ALTER PROCEDURE [DBO].[SPIDENTIFIERS] 
(
    @IDENT NVARCHAR(MAX) OUTPUT
)
AS
BEGIN
    SET NOCOUNT ON;
    SET @IDENT = REPLICATE(CAST(N'X' AS nvarchar(MAX)), 4001);
    -- Uncomment this to watch the fireworks
    --SET @IDENT = REPLICATE(CAST(N'X' AS nvarchar(MAX)), 4001);
END

Biml

I'm a big fan of using Biml, the Business Intelligence Markup Language, to describe my solutions as it allows the reader to recreate exactly the solution I describe without all those pesky mouse clicks.

1. Download BIDS Helper and install or unzip
2. Add a new biml file to your SSIS project
3. Fix the third line's ConnectionString to point to a valid server and database. Mine references localhost\dev2014 and tempdb
4. Right click on the saved biml file and generate package
5. Take your well deserved Biml break

Biml code follows

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <OleDbConnection Name="tempdb" ConnectionString="Provider=SQLNCLI11.1;Server=localhost\dev2014;Initial Catalog=tempdb;Integrated Security=SSPI;" />
    </Connections>
    <Packages>
        <Package Name="so_30460630" ConstraintMode="Linear">
            <Variables>
                <Variable DataType="String" Name="MyVariables">0</Variable>
            </Variables>
            <Tasks>
                <ExecuteSQL
                    ConnectionName="tempdb"
                    Name="SQL Ensure Objects Exist">
                    <DirectInput>
                        <![CDATA[IF NOT EXISTS
(
    SELECT
        *
    FROM
        sys.procedures AS P
    WHERE
        P.name = N'SPIDENTIFIERS'
)
BEGIN
    EXECUTE sys.sp_executesql N'CREATE PROC dbo.spidentifiers AS SELECT ''stub version, to be replaced''';
END
GO
ALTER PROCEDURE [DBO].[SPIDENTIFIERS] 
(
    @IDENT NVARCHAR(MAX) OUTPUT
)
AS
BEGIN
    SET NOCOUNT ON;
    SET @IDENT = REPLICATE(CAST(N'X' AS nvarchar(MAX)), 4001);
END
]]>
                    </DirectInput>
                </ExecuteSQL>

                <ExecuteSQL
                    ConnectionName="tempdb"
                    Name="SQL Using an OUTPUT parameter">
                    <DirectInput>EXECUTE dbo.SPIDENTIFIERS ? OUTPUT;</DirectInput>
                    <Parameters>
                        <Parameter DataType="String" VariableName="User.MyVariables" Name="0" Direction="Output" Length="-1" />
                    </Parameters>
                </ExecuteSQL>

                <ExecuteSQL 
                    ConnectionName="tempdb" 
                    Name="SQL Breakpoint">
                    <DirectInput>SELECT NULL AS nothing;</DirectInput>
                </ExecuteSQL>
            </Tasks>

        </Package>
    </Packages>
</Biml>

Answer 2

1. UserID needs to be in the readwritevariable section, not the read section, so that you allow the task to write into the variable.
2. parameter direction should be "output" since you are passing it out of your task not into it.

Answer 3

Your stored procedure parameter is OUTPUT but your SSIS package defines it as INPUT. Depending on the application, RETURNVALUE could also be used but from the syntax of your SP it is using an Output Parameter not a Return Value.

Verify the User:Id variable has the correct datatype. Try executing the SP in SSMS manually to verify that it runs without error and returns the expected result.

Also I think you are mixing OLEDB and ADO.NET syntax.

If using an OLEDB Data connection then you use the ? parameters in the query and the Parameter names must be "Parameter0 (and Parameter1, etc if more than 1)". Note: parameter names are zero indexed. In SP with more than 1 parameter the correct order is required.

If using an ADO.NET DataConnection then the query is just the named of the stored procedure, IsStoredProcedure=True, and the Parameter names matches the name of the parameter in the SP.

From your screenshots you currently are using named parameters and OLDEDB ? syntax. I don't believe that is ever valid. It is one or the other depending on the connection type.

Answer 4

• You need to keep the sql statement as "EXEC SPIDENTIFIERS ? OUTPUT**
• direction of variable should be Output in parameter mapping tab and "Parameter Name" should be exactly same as of input parameter defined in stored procedure or you can just use 0 instead of giving the actual name.