I have an SSIS package which populates almost all my dimension table.
The package populates around 20 tables..The package does not deal with much of logic..It just reads staging and writes into destination for 80 percent of those 20 tables. All tables in staging and destination are of Nvarchar and Bigint type..
My package has 3 big containers which has multiple containers inside them for each table..every small container for the table deals with truncating,inserting a row for unknown member and a data flow task which just reads staging and populates the staging.
Now the problem..we have 6 very large Junk dimensions..which we need at this point of time. ( don want to talk abt design stuff).The last big container fills one of the big dimension..e.g say sales order.which has 40 M records..we have 3 parallel processing
which look up to those 40M records and another 80 M records to populate other 4 large dimension. I have observed a significant decrease in perf once it reaches ther.It just fails to swap buffers and data tranfer gets very slow and at last it fails..Those DFT
includes union,derived column tranformation, split and Lookup ( which takes almost all mem)..i have seen the memory consumption increases rapidly)..That specific container takes almost 18 GB of space for look up and we have only 40 GB of space..
Is there a way to control
View Complete Post