SSIS Output Parameter error

  • 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

  • Try putting

    SET NOCOUNT ON

    at the top of your proc.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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.

  • 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

  • Do you have anything on the resultset tab?

    Edit - well done on solving it.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply