In one of the packages in production, the fact table load had a lookup done on a large dimension table. When the dimension table had 2 million records the package took 25minutes to load. The dimension table is loaded from sources in three different data flows. As the load window was 30 mts we looked at ways to improve it, luckily SSIS 2008 has a new transformation called Cache Transformation, which when used brought down the load time to 5 minutes, a huge performance gain!!!
We failed in efforts to update the cache and gave up until we stumbled on this article, which points at an alternate way to update the cache.
