Hey Guys,I have below scenario -
I am pulling data from table A from database D1 on Server S1 and want to load data into a table B from database D2 on Server S2. I also want to do some transformations like Lookup with same table B from database D2 on Server S2, some derived columns, some datatype conversions etc before loading it into table B from database D2 on Server S2. My SSIS package will be deployed onto Server S3. So to summarize the scenario, I will be running a package off S3 which pulls data from S1, does some transformations and loads data onto S2.I have two ways to achieve this -
1. Using Single Data Flow Task which will pull the data from S1, use all lookups and other transformation in the same Data Flow Task and load it into S2 Server.
2. I will create one staging table on S2 server where I will be pulling all data from S1 using one Data Flow Task and then use other Data Flow Task to read data from S2 staging table, do all lookup and other data transformations and load data into table B on S2.
Look ups in both the approch will have full caching and lookup data will grow as it will be incremental load.
I am somehow considering using approach 2 to gain on performance side considering full caching and lookup on the fly will slowdown the process and also will increase the network overload between the 3 servers. Any thoughts/suggestions?