SSIS OLE DB SQL COMMAND - Getting return value from nested stored procedure

Question

In SSIS I have an OLE DB SQL Command in my Dataflow that executes a stored procedure which in turn executes a 'nested' stored procedure (uspMyNestedSP). I can't seem to get the RETURN from the nested stored procedure. All SSIS returns is the int @RETURN_VALUE and not the string returned in the stored procedure.

Note, I cannot change uspMyNestedSP.

I have tried using OUTPUT parameters and setting the RETURN to this but all I get back is an empty string.

I need to do it in a OLE DB SQL Command on a row by row basis.

The only other way I can think of is to split the Data flow into two Data Flows and have a SQL TASK do it in between?

Here is the stored procedure:

ALTER  PROCEDURE [dbo].[uspMyMainSP]
    @_EthnicityIOCode NCHAR(20),
    @_EthnicityDescription NVARCHAR(60)
AS
    DECLARE @EthnicityCode NCHAR(8) 
    DECLARE @EthnicityDescription NVARCHAR(60)
    DECLARE @AuditMessage NVARCHAR(128)

    SET NOCOUNT ON;

    IF LEN(TRIM(@_EthnicityIOCode)) > 0 AND LEN(TRIM(@_EthnicityDescription)) > 0
    BEGIN
        SELECT  
            @EthnicityCode = ethEthnicityCode,
            @EthnicityDescription = ethDescription
        FROM    
            TC36sp1.dbo.Ethnicity
        WHERE   
            (ethIOCode = @_EthnicityIOCode)

        IF @@ROWCOUNT = 0 
        BEGIN
            --  Insert
            EXEC uspMyNestedSP '*df', @_EthnicityDescription, @_EthnicityIOCode, 1
            SET @AuditMessage = '''' 
        END
        ELSE
            IF @_EthnicityDescription <> @EthnicityDescription
            BEGIN
                -- Update
                EXEC uspMyNestedSP '*df', @_EthnicityDescription, @_EthnicityIOCode, 1
                SET @AuditMessage = ''''
            END

    -- This is what I want to return back to SSIS!...
    RETURN @EthnicityCode 
END

The @EthnicityCode string is what I want to return. This works fine and returns when run manually in SSMS.

Here is my SQL Command in SSIS:

EXEC  ? =uspMyMainSP ?, ?

I don't think OLEDB Command can handle any output. I use a script component for items like this. It is more common practice when you need to know you identity value. — KeithL

KeithL KeithL · Accepted Answer · 2020-08-06T14:51:53

Here's how to do it with script component:

        string sql = "dbo.uspMyNestedSP";
        using (OleDbConnection conn = new OleDbConnection(cstr))
        {
            using (OleDbCommand cmd = new OleDbCommand(sql, conn))
            {
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@p1", Row.[ColumnName]);
                cmd.Parameters.AddWithValue("@p2", Row.ColumnName);
                conn.Open();
                string EthnicityCode = cmd.ExecuteScalar();
            }
        }

SSIS OLE DB SQL COMMAND - Getting return value from nested stored procedure

2 Answers