Post reply

EXECUTE SQL - result set

  • February 27, 2016 at 12:49 am

    #325077

    Hi,

    I have a scenario where there are 2 tasks, first is EXECUTE SQL TASK, second is another EXECUTE SQL TASK, each of which calls one separate Stored Procedure.

    1) Depending on the value returned by first SP, a decision is made whether or not to run second EXECUTE SQL TASK.

    2) First SP has a final executable statement of SELECT @RESULT_VALUE AS RESULT_VALUE (i am working in PDW/APS/MPP, so, RETURN is not allowed)

    3) This return value is expected to be captured in first EXECUTE SQL TASK's RESULT SET (Single) mechanism, where a @user variable is used to captured it

    4) A PRECEDENCE CONSTRAINT is established between first and second tasks to decide whether or not to run the second task.

    in this setup, I get the error that the variable RESULT_VALUE is not found.

    could you please share your thoughts.

    thank you

  • February 27, 2016 at 4:03 pm

    #1861652

    Is the error a SQL Error or an SSIS Error?

    Is the @user variable you mention the actual name of the variable you are using in the package? How is it defined?

    What is the Expression you are using in the Precedence constraint?

    I just did a quick test package (not against PDW\APS) where EST 1 does a Select with a single row, single column result set being assigned to an INT32 variable called @ResultSet and my precedence constraint is Success and Expression (@[User::ResultSet] == 1) and when I return 1 EST 2 runs and when I return any other value I EST 2 does not run.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • February 27, 2016 at 5:01 pm

    #1861660

    Hi Jack

    I found out the cause, but am yet to figure out the resolution.

    My setup is this:

    SSIS (Execute SQL Task) ---> Primary Stored Proc with SELECT Statement at the end, acting effectively as a popular RETURN mechanism

    However, above SP is calling another SP, which is also flusing a SELECT as return value.

    So, the effective setup is:

    SSIS (Execute SQL Task) ---> Primary SP --> Secondary SP

    The outcome is SSIS is expected to handle 2 return values, and thus I upgraded by Execute SQL Task component to "Full result Set" and captured both return values as an "Object" data type variable.

    Next, I used a ForEachLoop with ADO enumerator as downstream component with 0 and 1 as index-es to place-hold the two returned values.

    Within FELoop Container, I used a VB2010 scriptTask component interpret the two return values hidden in "Object" type. My goal is to populate a global variable with one the two values held by the object variable, and use a Precedence-Constraint on that global variable to decide whether or not execute a Downstream task.

    Here is where I am challenged....the FELoop container is able to read only 1 index. If I use 2 index-es (as in my case there are 2 StoredProcs flushing output), then i get the notorious error something like below:

    Error: The enumerator failed to retrieve element at index "4".

    Error: ForEach Variable Mapping number 5 to variable "User::Column5" cannot be applied.

    The two SPs I am invoking are organisation wide utilities, I have little opportunity to tailor their interface - I have to use them as they are.

    Can you please throw some light on the above error. Why its working with only 1 index, why it fails when 2nd Index is introduced. Why Execute SQL task is succeeding with an Object slotted to place-hold 2 variables?

    thank you

  • February 27, 2016 at 5:29 pm

    #1861661

    I think your EST needs to be configured like this:

    Notice I have 2 results being returned.

    And then your For Each Loop need to be configured like this:

    Then you don't even need a script task because the last value in my ScalarResult variable is the last result returned.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • February 27, 2016 at 5:43 pm

    #1861663

    Yes Jack, I too had "rows in all tables" option chosen. And had the below error

    Sorry for asking this, would you kindly declare 2 indexes and see? Because, I have success with 1 index, and when I use 0, 1 index-es, the program fails.

    My error message is:

    The enumerator failed to retrieve element at index "1".

    ForEach Variable Mapping number 2 to variable "User::xxxx" cannot be applied.

    ForEach Variable Mapping number 0 to variable "??" cannot be applied.

  • February 27, 2016 at 8:55 pm

    #1861669

    Is one result set returning 2 columns and the other only returning 1 column? That would explain the error.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

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

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