Passing full result set OUTPUT as an INPUT to WHERE IN ( ) clause


  • I have a variable 'vSOMIDS' which is of type object.

    I have a sql task that reads a column of string values into a full result set.

    I want to be able to reference this variable in an 'OLE DB Source" SQL command, within the same package as SELECT A,B,C where IN ( **variable from above which is a list of items**).

    Any help greatly appreciated please!!!

    čtvrtek 14. června 2018 5:04

Všechny reakce

  • Easiest wy to do this is like below

    1. Instead of object variable, create a variable of type string

    2. In your sql task instead of returning it as a resultset return it as csv list as a single string value. you can use FOR XML PATH for this. Set resultset option to single row and assign the csv to the above string variable created 

    3. Add a new variable to hold the query 

    set expression like


    "SELECT A,B,C from table where column IN (" + @[User:StringVariable] + ")"

    StringVariable represents the variable created in step 1 and holding csv list from sql task

    4. For your OLEDB Source use Data Access Mode as SQL Command from variable and map to the Query variable created in step 3

    then it will work as per your requirement

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    čtvrtek 14. června 2018 6:08
  • Hi marleon,

    Assume your full result set mapped to the object variable vSOMIDS, then you can add a For Each Loop container to iterate your items with Foreach ADO Enumerator.

    Then add another string variable to receive the current item for each loop.

    Next you can add the Data Flow Task inside the For each Loop container and use the variable CurrentItem in your OLE DB Source command line combined with the expression.

    You can refer to following example:

    Execute SQL Task in SSIS Full Row Set


    Pirlo Zhang 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact

    čtvrtek 14. června 2018 6:28