Post reply

Lookup task with Recordset variable

  • March 2, 2010 at 2:23 am

    #139142

    Hi,

    I have a recordset in my package populated with the TypeIds of data I want to include. Now I would like to use a Lookup task to identify those records in the source data that have TypeIds in the recordset. But the Lookup task does not seem to be able to lookup against a recordset.

    I guess I could write the recordset out to a table, then user that table in the lookup, but that seems to go against SSIS's philosophy of "doing everything in memory". But there is also no RecordSet data flow Sources, so I would need to write a script component to implement it.

    Which indicates to me that I might as well write a parameterised stored procedure to perform this work - SSIS just seems to be causing me to waste a lot of time jumping through hoops.

    Or am I missing something?

  • March 2, 2010 at 3:11 am

    #1126918

    I don't know how you create your recordset, but couldn't you store the records in a temp table instead of a recordset and use this table for your lookup?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • March 8, 2010 at 12:25 am

    #1129863

    xnl28-574517 (3/2/2010)

    Hi,

    I have a recordset in my package populated with the TypeIds of data I want to include. Now I would like to use a Lookup task to identify those records in the source data that have TypeIds in the recordset. But the Lookup task does not seem to be able to lookup against a recordset.

    I guess I could write the recordset out to a table, then user that table in the lookup, but that seems to go against SSIS's philosophy of "doing everything in memory". But there is also no RecordSet data flow Sources, so I would need to write a script component to implement it.

    Which indicates to me that I might as well write a parameterised stored procedure to perform this work - SSIS just seems to be causing me to waste a lot of time jumping through hoops.

    Or am I missing something?

    If you can use third-party components, I would recommend you check the commercial CozyRoc Lookup Plus component. You can create in-memory dictionary using the Dictionary Destination script (to be used with CozyRoc Script Component Plus). And then you can provide the variable containing the dictionary to the Lookup Plus.

    ---
    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Viewing 3 posts - 1 through 2 (of 2 total)

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