Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS Output Parameter error Expand / Collapse
Author
Message
Posted Friday, June 14, 2013 8:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 31, 2013 11:32 AM
Points: 3, Visits: 26
Hi all,
I am attempting to run a stored procedure with a single output parameter and then map it to a variable. Seems simple in theory, but I keep getting the error
"There is an invalid number of result bindings returned for the ResultSetType"

After looking at all the answers i can find, none seem to answer my problem.

the SP:-

Create Procedure ReturnsessionVariables(@sessionid INT,@groupshare varchar(2000),@returnvalue nvarchar(max) output)
as
begin
DECLARE @guid NVARCHAR(8)

SELECT @guid = guid from THANDLERSESSION where sessionid = @sessionid

select @returnvalue = substring((SELECT ','+keyname+'='+case when CHARINDEX(@guid,keyvalue) != 0 and keyvalue != @guid then @groupshare + right(keyvalue,len(keyvalue)-CHARINDEX(@guid,keyvalue)) else keyvalue end
from THANDLERPARAMS HP
where sessionid = @sessionid
FOR XML PATH ('')),2,2500)

end

Basically i am reading all the parameters from a table and creating a concatenated string that can then be passed to the next step in my package.
Output Example :-
hrfile=tvdev2.xlsx,skipexceptions=0,appenddata=-1,emailpassword=-1

this proc works fine and returns the data when I run it in SQL.

In SSIS i am attempting to run the proc and return the value to a variable.




As far as i can see I am doing everything ok, but i cannot work out what I must be doing wrong. This is a single return value proc so no issue with more than 1 row, object assignment etc. So what i am doing wrong?

Thanks


  Post Attachments 
ExecuteSQL Screen.png (29 views, 20.58 KB)
Parameters.png (29 views, 19.02 KB)
Post #1463598
Posted Friday, June 14, 2013 8:26 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 11:33 PM
Points: 4,983, Visits: 11,676
Try putting

SET NOCOUNT ON

at the top of your proc.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1463603
Posted Friday, June 14, 2013 8:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 31, 2013 11:32 AM
Points: 3, Visits: 26
Hi,
Thanks for the suggestion
Unfortunately, it has made no difference. Still the same error getting returned in SSIS when I try to run it.
Post #1463607
Posted Friday, June 14, 2013 8:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 31, 2013 11:32 AM
Points: 3, Visits: 26
It's amazing what randomly changing things does.

It seems that because I had a result set as singleRow it was expecting a dataset to come back , and it does not interprete an output value from a Stored Proc as a dataset.

By simply changing the resultset value to "None", the issue has gone away and my value is being returned fine.

Cheers
Post #1463619
Posted Friday, June 14, 2013 8:46 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 11:33 PM
Points: 4,983, Visits: 11,676
Do you have anything on the resultset tab?

Edit - well done on solving it.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1463620
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse