Is there anyway to perform outer join in SSIS without using Merge Join transformation?

Question

I need to do many left joins to create my fact table which has more than 150 M Records. When i do outer join using merge join and sort transformation, it takes many hours to load data. So need a help to do this without merge join transformation.

Answer 1

The fastest way to do this is to load the data directly into staging tables on your destination database server, and then run a stored procedure that does the joins to load from the staging tables to the fact table. If the staging tables are indexed on the join keys, that will be the fastest solution.

In the SSIS dataflow, you can use the Lookup transformation instead of the Merge Join to do the same outer join, but it's even slower than the merge join, so if performance is what you're after, it's not a good solution.