(ssis) to move certain tables with common where conditions to another DB

25112
25112 used Ask the Experts™
on
is SSIS a good/right way do this scenario:

server A-> DB1 has 260 tables.
server B -> DB1 has 80 tables. These tables schema are from server A-> DB1.

we need to refresh data from
server A-> DB1 to
server B-> DB1 for 80 tables often. but only for a subset of data.

during a refresh, we need to truncate/delete the 80 tables in server B -> DB1.. then, load only those 80 tables fresh, but only selective records as below example:

example:
table1 - select * from table1 where key1 in (5 values...)
table2 - select * from table2 where key1 in (5 values...)
table3 - select * from table3 where key1 in (5 values...)
table4 - select * from table4 where key1 in (5 values...)
............
table79 - select * from table79 where key1 in (5 values...)
table80 - select * from table80 where key1 in (5 values...)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
if ssis, then should I hard code these 80 statements?

is the performance dependent on the # of records in each of these tables? some of these tables have only 100s of records, others have millions of records in server A-> DB1. But after the where condition, it can become very less, relatively speaking.
Data Architect
Most Valuable Expert 2013
Author of the Year 2015
Commented:
>if ssis, then should I hard code these 80 statements?
Yes, regardless of the situation.  SSIS requires a 'contract' between itself and any data source, and cannot handle anything 'dynamic'.

>we need to refresh data from
> but only for a subset of data.
Define this.  If we're talking only the recent day's data + any changes, then a Change Data Capture source or Upsert would be more appropriate here.

>table1 - select * from table1 where key1 in (5 values...)
Any change these '5 values' can be in a table in the source, and then a view be created for each table that goes something like 'SELECT * FROM MyTable AWHERE key1 IN (SELECT key1 from the_list)?

Author

Commented:
>>If we're talking only the recent day's data + any changes
No. everytime there is a refresh request, those 80 tables will be delted/truncated and only then SSIS run,

>>Any chance these '5 values' can be in a table in the source
Does this improve performance?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial