Post reply

Is there a way to use a variable in a control like a Lookups SQL?

  • May 22, 2009 at 11:56 am

    #137972

    So say I'm looking up an address. If I just select the table, it's doing a SELECT * and getting back all 250k rows.

    So I use a select statement to limit it to the 3 fields I need in the Lookup. Far faster.

    Now since I'm looking for addresses for a school, I can use SQL to limit the addresses returned to those that are assigned to a school. So down from 250k to 40k.

    Now in a Package Variable I have I hold the ClientId (of our clients, each client has an ID number that indicates their data). So if I could include this User::ClientId into the query I could reduce the data every lookup has to pull by about 75%. Also I wouldn't have to include the ClientId on every single record to match on the lookup return values.

    Is this even possible?

  • May 24, 2009 at 10:43 pm

    #999559

    If you have a column with the value you are after, you can use the advanced tab in the Lookup task to apply a filter to the SQL statement. If the value you wish to use is in a variable, you would need to add it to the pipeline (perhaps using a Derived Column transform)

  • May 24, 2009 at 10:53 pm

    #999560

    Oh wow, thanks a million! I kept hitting that Advanced tab and kept seeing Memory Restriction and something about caching and didn't realize the bottom portion was a way to modify the query by hand.

    I'll have to play around with this, but it looks like it's acting like I would want, using the parameters as a WHERE. I knew portions of SSIS just RBARed through things and I was afraid this was one of those spots where it was just spinning through rows that don't even come close to applying.

  • May 27, 2009 at 6:43 am

    #1000529

    Normally, I would not be too concerned about SSIS and RBAR. SSIS is pretty good at handling that sort of thing. You may well find that the way you are using the Lookup task is a little like this - whilst it will cache what it finds, the normal behaviour (which is to pre-load the lookup cache before package execution starts) becomes cache what it finds based on the data that passes throught the lookup task.

    I certainly would be careful with tasks that are home grown (such as Script Tasks and Custom built tasks). As is the case with any application, some code is good and some isn't.

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

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