SSIS Moving Content From Views from one database to Another

Question

One question though lets say publisher database had 100 tables and I use Transactional Replication to move the data from those 100 tables to Subscriber Database that would be fine.

But lets say I don't want the 100 tables but i want to create 3-4 Views which contain the key information I want from those 100 tables. How Would I achieve this.

1) Firstly I guess the views need to be created on the publisher database 2) Secondly Do i need to create then 3/4 Tables in the Subscriber database which have the same columns as the view from publisher database. 3) What sort of replication or maybe even SSIS or something to move the data from the publisher view to subscriber database

Answer 1

Replication probably wouldn't be viable or as performant an option as creating a SSIS package for transferring data from those views and into the small set of tables in the remote database. SSIS's strongest feature is it's ability to transfer large volumes of data quickly from a source and into a destination. With a little upkeep, you could potentially just transfer the differences between the two databases at some scheduled interval and have a fairly flexible solution.

Answer 2

SSIS will be the better solution. You would create the tables on your target database. Then, you can create the SSIS pacakge(s) to populate the target tables.

SSIS can use queries on tables or views. And, it can also execute a stored procedure to retrieve the data.