none
getting error on parameter mappings RRS feed

  • Question

  • Hi we run 2017 std.   I chose "parameter mapping" instead of "result set" in my exec sql task to map 22 returned cols (from a tested query that returns one row) to 22 vars.

    I'm hoping I can salvage what I did.

    I have result set = "Single row".    I have "Direction" on each mapping = "Output".  I have all data types in the param mapping dialogue as varchar.    In each Parameter Name I specifically listed the column name returned from the query.  I left all parameter sizes = -1.  All of the vars are defined to ssis as string.

    The error i'm getting looks like this...

    [Execute SQL Task] Error: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_SingleRow".

    Is there an easy way to deal with this?  I tried setting result set = "None" and got this error...

    [Execute SQL Task] Error: Executing the query ";with vertical as
    (
    select   xxx.yyyyyyyy ,
         ..." failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    I checked every Parameter Name in the Parameter Mapping dialogue and they all match the col names returned by the query.  There is a cte involved.  It returns organization, low date cast as varchar , high date cast as varchar.  The query essentially pivots this data so every org's low and high come out on one row with col names looking pretty much like LOWxxx and HIGHxxx where xxx is the org abbreviation.   

     

    • Edited by db042190 Friday, September 13, 2019 4:50 PM clarity
    Friday, September 13, 2019 4:42 PM

Answers

  • Hi db042190,

    Here is how it works. There are 3 types of parameters for the SSIS Execute SQL Task:

    • INPUT – Used for Input variables. The value will be passed from calling environment.
    • OUTPUT – Used for Output variable and value will be returned to calling environment.
    • RETURN VALUE – The value which is returned by SP.

    Based on the stored procedure below, for the Parameter Mapping settings, Direction column:

    • @ID - INPUT
    • @City - OUTPUT
    • 770 - RETURN VALUE

    CREATE PROC dbo.usp_goodName
    (
    	@ID INT
    	, @City VARCHAR(30) OUTPUT
    )
    BEGIN
    	SET NOCOUNT ON;
    
    	SET @City = 'Miami';
    	
    	RETURN 770;
    END
    • Edited by Yitzhak Khabinsky Friday, September 13, 2019 5:30 PM
    • Marked as answer by db042190 Friday, September 13, 2019 6:18 PM
    Friday, September 13, 2019 5:21 PM
  • thx so i'm concluding this...

    had I put the word output after each returned col in the query, the mapping would have worked.

    instead, I used the "result set" option (not parameter mapping) and everything looks ok. 

    • Marked as answer by db042190 Friday, September 13, 2019 6:19 PM
    Friday, September 13, 2019 5:34 PM

All replies

  • I named each column in order with a relative number from 0 to 21.  I got the invalid number ... error again.
    Friday, September 13, 2019 5:07 PM
  • Hi db042190,

    Here is how it works. There are 3 types of parameters for the SSIS Execute SQL Task:

    • INPUT – Used for Input variables. The value will be passed from calling environment.
    • OUTPUT – Used for Output variable and value will be returned to calling environment.
    • RETURN VALUE – The value which is returned by SP.

    Based on the stored procedure below, for the Parameter Mapping settings, Direction column:

    • @ID - INPUT
    • @City - OUTPUT
    • 770 - RETURN VALUE

    CREATE PROC dbo.usp_goodName
    (
    	@ID INT
    	, @City VARCHAR(30) OUTPUT
    )
    BEGIN
    	SET NOCOUNT ON;
    
    	SET @City = 'Miami';
    	
    	RETURN 770;
    END
    • Edited by Yitzhak Khabinsky Friday, September 13, 2019 5:30 PM
    • Marked as answer by db042190 Friday, September 13, 2019 6:18 PM
    Friday, September 13, 2019 5:21 PM
  • thx so i'm concluding this...

    had I put the word output after each returned col in the query, the mapping would have worked.

    instead, I used the "result set" option (not parameter mapping) and everything looks ok. 

    • Marked as answer by db042190 Friday, September 13, 2019 6:19 PM
    Friday, September 13, 2019 5:34 PM
  • Hi db042190,

    You came to the right conclusion.

    Please don't forget to click "Mark as Answer" the response(s) that resolved your issue. This can be beneficial to other community members reading this thread.

    Friday, September 13, 2019 6:10 PM