CODEWITHSUNDEEP
sql-serverssis
nick
nick

Reputation: 333

SSIS execute sql task returncode from SP

I am trying to get a returncode from my SP and then based on its value (0 or 1) it will go to next task. 

Create Procedure sp_test
AS
DECLARE @cmdline VARCHAR(500),
@ReturnCode INT 

SELECT @cmdline = 'dir z:'

CREATE TABLE #temp (SomeCol VARCHAR(500))

INSERT #temp
EXEC @ReturnCode = master.dbo.xp_cmdshell @cmdline

IF @ReturnCode <> 0
BEGIN
    SELECT SomeCol   
    FROM #temp
    WHERE SomeCol IS NOT NULL
END

DROP TABLE #temp

RETURN @ReturnCode
GO

Now inside my Execute SQL task of SSIS, Here is what I type and specify ResultSet = Single Row

DECLARE @return_value int
EXEC    @return_value = [dbo].[sp_test]
SELECT  'ReturnValue' = @return_value

enter image description here

enter image description here

Here is the error I am getting,

[Execute SQL Task] Error: An error occurred while assigning a value to variable "Variable": "Unable to find column ReturnValue in the result set.".

I need that ReturnCode Value so that I can use it for my next task.

Upvotes: 1

Views: 1513

Answers (2)

Ajeet Verma
Ajeet Verma

Reputation: 1123

You have to try below logic...

Put the below code into SQL Statement .

DECLARE @return_value int
SET @return_value=?
EXEC    ? = [dbo].[sp_test]
SELECT  'ReturnValue' = ?

And set the result set as in snapshot..

enter image description here

Upvotes: 2

billinkc
billinkc

Reputation: 61269

Assuming OLE DB provider, the Result Name should be 0 as it's the zeroeth element of the result set. Also, ensure your Result Set type on the General tab is ... Single Row or whatever the label is.

Also, no need for a Parameter Mapping as your stored procedure does not accept parameters (input or output)

Upvotes: 4

Related Questions