When do a fact table load...I have to perform lookups against the dimension tables. The dimensions tables I have support slow changes, however, and thus have multiple rows for a single legacy key under different effective start and end dates. In order to do this lookup, I have to not just join on the legacy key, but also validate that the date of the transaction I'm loading is between the effective date range of the dimension item. It seems the Lookup task only supports equijoins. Am I missing something here How is this accomplished if you can use greater than or equal to and less than type join conditions
SSIS Non-Equijoin Lookups
Hot Topic
- ASPNET account permissions
- Can't install Express June CTP?!
- how to get SQL SERVER 2005 NETWORK CONFIGURATION...
- Failed Install of SQL Server 2005 SP1 CTP
- Sotred Procedure - Bulk Insert - parameter
- Is it possiable to store password encrypted to an external config file?
- SQL Express Management Setup Error on Windows Vista Beta 2
- SQL 2000 EM Prob. after 2005 install
- TargetServerURL for named instance?
- strange behavior when trying to open report in Report Manager
SQL Server
- Reporting Services Setup Issue
- Failed to generate a user instance of SQL Server
- Using Sql Express databases with Windows Forms applications
- Question on Report Builder
- sql 2005 updating table definition with 'large' amounts of data - timeout
- Certificate creation problem
- How to use SQL Express to Cache the data.
- Results to File
- Experts required to validate these statements of fact
- Using Reporting Services 2005 on SQL Server 2000?
SSIS Non-Equijoin Lookups
Lamy Xiang
There is a possible solution described here: http://sqljunkies.com/WebLog/tpagel/archive/2005/08/31/16585.aspx
Donald Farmer
jdk
How about the Advanced tab on the Lookup task that lets you override the query used for the lookup That's worked for me flawless in the past. The solution you pointed to is less than ideal on several fronts...1) It creates a cartesian product which hurts performance and just keeps the computer busy munching on data it will never use 2) It is often the case in where the source data and lookup table data are from different databases or from different servers which makes the inner join statement much more difficult to create and be dynamic rather than hard coded.
Utilizing the query override on the Advanced table you can modifiy the query to be a proper lookup that involves an equijoin on the ID and less than or equal to and and greater than or equal to join on the effective dates.