September 6, 2011 at 8:27 am
Hi All,
I have a dimension table load from staging DB that loads 85 million records into the dimension table.
The package has partial cache lookup for a "service weight" column based on mapping 2 columns between source and looked up table and that a date column in source is between the looked up tables's corresponding date columns' effective and expiry date.
So that makes it 2 column mappings and 2 date comparisons. I do this using a partial cache and mention the query in the advanced tab of the SSIS lookup transformation.
Since the partial cache caches the record set at run time, the package executes for a longer time.
Is there anyother alternative I can achieve the same result without a partial cache lookup?
Regards,
Josh
September 6, 2011 at 1:45 pm
joe_2110 (9/6/2011)
Is there anyother alternative I can achieve the same result without a partial cache lookup?
Regards,
Josh
The short answer to your question is yes...there are a few alternatives. If you are intent on using the lookup transformation, you have the "Full Cache" mode as well as the "No Cache" mode. See the following: http://blogs.msdn.com/b/mattm/archive/2008/10/18/lookup-cache-modes.aspx
With so many records though, the lookup transform will never perform well (RBAR). A better solution would be to stage the data, and use the power of the SQL language to perform a set-based update.
I don't want to be presumptuous, but you may have a bigger issue here...a design issue. It almost sounds like you have dimensions that are dependant on each other, and you may want to try and stay away from that as much as possible.
Your description of the lookup raised a red flag in my mind, but please disregard if it isn't relevant.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply