Can someone advise whether it is possible to use a lookup transform on a fact and dimension table that are joined on several keys? The dimension table doesnt have a surrogate key and is joined by two business keys. Doesn't seem to work for some reason, everything is going to no matches output. Any help would be appreciated
Yes, you can map multiple columns to each other in the lookup transform.
I'd suggest you check your data types and ( if applicable ) the widths of the columns. You may need to use a derived column or a data conversion task to "prep" one side of the data being compared to get a match. This would also apply to code pages in case you do not have all of your data in the same code page.
Hope it helps,
I had a similar issue once and it had to do with padding, so I trimmed everything and it worked.
Thanks for clearing that up guys, just never seen multiple joins in any of the books I have. Data type is the same, size is different. I'll try conversion and see how that goes.
The data conversion did the trick, worked like a charm. Thanks Keith.
Strange, SQL queries are not an issue but then again, a newbie in SSIS