CODEWITHSUNDEEP
sql-serverssis
user1254579
user1254579

Reputation: 4009

Passing a value to T-SQL procedure and getting an output value from the procedure using execute sql task SSIS

I have a scenario like this:

I need to pass @id to the stored procedure and get the @key from the procedure using SSIS Execute SQL task:

create PROCEDURE lProcedurename
(@ID int ,
@key int  output
)
with execute as caller
as
set nocount on;
begin
BEGIN TRY
set @key=999
END TRY
begin catch
----some codes
end catch
end

Please find the execute sql task properties

result set as single row

I have already given the execute sql statement (ssis execute sql task level) as 

exec lProcedurename  @ID = ? ,  @key = ?

parameter mapping as 

variablename ,direction, data type, parametername ,parameter size
user::id input long  o  -1
user::key output long 1 -1

result set as

resultname,variablename
0   user::key

But when I run the package it errors.. Please let me know is the property setting is correct ?

please see the error

*SSIS package "package1.dtsx" starting. Error: 0x0 at CMD Audit OnPreExecute: No result rowset is associated with the execution of this query. Error: 0xC002F309 at CMD Audit OnPreExecute, Execute SQL Task: An error occurred while assigning a value to variable "key": "Exception from HRESULT: 0xC0015005". Task failed: CMD Audit OnPreExecute

for another run of the same package i can see another error as SSIS package "package1.dtsx" starting. Error: 0xC001405B at Event Package On Error: A deadlock was detected while trying to lock variable "User::key" for read access. A lock could not be acquired after 16 attempts and timed out. Error: 0xC00291EA at Event Package On Error, SQL Task: Variable "User::key" does not exist. Error: 0xC0024107 at Event Package On Error: There were errors during task validation. Warning: 0x80019002 at OnError: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (5) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. Error: 0x0 at testing: No result rowset is associated with the execution of this query. Warning: 0x80019002 at OnError: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (5) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.*

Please be noted that the user::key variable has already been defined

Upvotes: 1

Views: 7771

Answers (2)

Koit
Koit

Reputation: 41

I had same problem and this forum gave me a hint and I got it working. In my Stored Procedure I used input parameter and output parameter and in catch block I used RETURN 1, that I wanted to use as result set when the procedure fails. In another procedure it worked such way but there I didn't use output.

What I did before (where ? 1 is input and ? 2 output in parameter mapping):

EXEC procedure ?, ? OUTPUT

and I was expecting that it gave me a result too when I use RETURN (it was OK when I called the procedure in SSMS T-SQL). But the result was an error.

What I changed:

EXEC ? = procedure ?, ? OUTPUT

where ? 1 is the answer of RESULT, ? 2 is input and ? 3 is output. I used all of them in parameter mapping and set ResultSet to none.

Upvotes: 4

William Salzman
William Salzman

Reputation: 6446

Your parameter mapping should be fine. You need to set the resultset to "None" and remove the resultset mapping. You are trying to both capture the output parameter in a variable and set it to a resultset. The problem with this is that your sql in your stored proc will not return a result.

Upvotes: 2

Related Questions