Previously, we IT team developed an ETL package to create and load tables that exist in source database(oracle) but not in target database(sql server).
As it may come to your mind, in the ETL package we use ForeachLoop and Oracle's sysobject to generate 'create table..' scripts to build the tables in Sql server then use script task to load data into the tables which have different structure through bulkcopy.
We did not perform delta loading. Just in a drop-create-load route.
Now, a performance issue comes. It takes almost 30 hours to generate and load all these more than 400 tables. The number and data volume are increasing, so we have to think about delta loading.
But how to do the delta maintenance on 400+ tables? please provide ideas.
Appreciate your help.
(Oracle10, SS2005, BIDS2005)
View Complete Post