Back in February I posted a blog entry telling you about an article I had written at SQLServerCentral.com called "The New ETL Paradigm". Happily it turns out that at least one person has downloaded the example package and had a play with it, that person being Marco Russo.
Part of the article explained a method for obtaining dimensional surrogate keys for insertion into a fact table using MERGE JOIN transformations. In another seperate article on SQLIS.com I have advocated using the LOOKUP transformation to do effectively the same thing.
I don't know if Marco has read that second article but he took this approach of using LOOKUP transformations and adapted my package to do some proper stress testing of the 2 approaches. He put 600000 rows through the pipeline and found overwhelmingly that using LOOKUPs is preferable to MERGE JOINs, primarily because the former is less resource intensive. Less resources invariably means quicker execution times which is after all what we're ultimately all after.
Marco has talked about his experiences here and its well worth a read. He had some issues around dealing with unknown dimension members which led to Marco, Michael Barrett Johnson and myself having a short discussion about the different approaches to dealing with this problem in the comments section of the blog.
-Jamie