Dynamic Lookup .... is it in SQL 2008?

I'm finding that not having the ability to dynamically change the contents of the query in the lookup transform is a major, major problem. Has anyone looked to see if this is in the SQL 2008 CTP?

Does anyone have any good work arounds?

Thanks,

Michael

Can you provide an example of how you vision this to work?|||

I have a very large table that's partitioned into 56 partitions. I bring in data based on partition and match on an ID field. Whether the data matches or not sends it through different transformations in the same data flow. The table itself has 1.2 billion records and about 4TB and is growing quickly. I'd like the lookup to only pull the records from the partition I'm working with. In the end (this is unrelated to the lookup), I just swap the new partition into the table. It's actually pretty quick. But the gist of it is I want to dynamically limit the lookup query to the range of the partition I'm working on. Right now, I'm using a View and changing the definition to use the partition being used before I go into the dataflow. The problem with that is now I can't run data loads in parallel. So, the other possibility is to have 56 different packages, at least for the data flow part.

Thanks,

Michael

|||

MichaelT wrote:

I'm finding that not having the ability to dynamically change the contents of the query in the lookup transform is a major, major problem. Has anyone looked to see if this is in the SQL 2008 CTP?

It doesn't appear in the current CTP. That is not to say it won't appear in a future CTP of katmai.

This is a question for Microsoft really so...

[Microsoft follow-up] Is there any information you guys can provide?

-Jamie

|||

Thanks, Jamie. You're right, there're no signifcant changes to the Lookup component in the current CTP.

Michael, I think you might be able to use an OLEDB Source, Merge Join, and Conditional Split to achieve your goal. What do you think?

|||

The current plan (subject to change and all usual disclaimers about non-released products) is to make SQL statement property expressionable - so you'll be able to dynamically change this query using property expressions on data flow task.

Does it work for your scenario?

|||

Yes, having an expressionable SQL Statement property for the Lookup Transform would work great for me. Hopefully, it'll make it to SQL 2008.

Thanks,

Michael

|||

Michael Entin - MSFT wrote:

The current plan (subject to change and all usual disclaimers about non-released products) is to make SQL statement property expressionable - so you'll be able to dynamically change this query using property expressions on data flow task.

Does it work for your scenario?

Michael,

That would be fantastic.

-Jamie