May 5, 2009 at 2:04 pm
I have a table of hotels.
For each city there are multiple hotels (2 or 3) and they have date ranges in which they are active.
In my data flow I need to lookup which hotel I need to use based on the CityID and the Event Date.
My query in SQL would be something like:
Select HotelID where HotelCity = EventCity
and EventDate between HotelBeginDate and HotelEndDate
How can I do this in a data flow?
Maybe a script component but I've never accessed a sql table within a script component.
Looking for a suggestion.
Thanks
May 5, 2009 at 2:10 pm
my experience is that the data flow engine doesn't have much performance gain to offer in complex joins where you may need one date in one table to be between two dater fields in another.
sounds like a candidate for a good old fashioned t-sql join.
In my etl processes...i typically have joins like that as ole db source that lookup against or merge with other datasets.
May 6, 2009 at 10:39 am
Consider creating a table of dates (see this article: http://www.sqlservercentral.com/articles/Date+Manipulation/65195/) and join your Hotels table to that new dates table. The resulting join would give you a discrete list of available dates rather than a range, which may be better suited for your Lookup Transformation.
hth,
Tim
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
May 6, 2009 at 3:56 pm
thanks tim,
Thought about doing that but since the hotel contracts sometimes expire but we keep using them so we defaulted the contract end dates to the end of time and that join would return a ton of rows (like close to a billion).
I ended up putting the staged data in a temp table and ran a sproc to update the hotel data.
thanks for the suggestion
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply