<div>
yes, i will create a reference table which will hold the latest set of tables that need data in serverb.<br />
<br />
i am open to any feature of ssis that can help.. but if we can automate as much as possible , is the best.<br />
<br />
for example, if the reference table added a new table xyz, is it possible to send the script of xyz automatically to the serverb and then do the transfer through another task? or should it be manually copy the new table schema and pasting and running it in serverB and then initiating a task for transfer?<br />
<br />
this may have to done frequently, so looking for best possible solution for long term<br />
<br />
thanks
</div>


yes, i will create a reference table which will hold the latest set of tables that need data in serverb.

i am open to any feature of ssis that can help.. but if we can automate as much as possible , is the best.

for example, if the reference table added a new table xyz, is it possible to send the script of xyz automatically to the serverb and then do the transfer through another task? or should it be manually copy the new table schema and pasting and running it in serverB and then initiating a task for transfer?

this may have to done frequently, so looking for best possible solution for long term

thanks

<div>
<div class="content wysiwyg-content">
The main database in Server A has 700 tables... but there is a database in ServerB that will have only a subset of it. (75-100 tables)..<br />
<br />
This list can vary from week to week or month to month.. and when there is a request to refresh the data, all the data of tables in the list has to be sent over with the replace.<br />
<br />
if the list is modified and some tables are added and some removed.. that should be reflected in the next refresh run.<br />
<br />
what tasks are needed to get this done in SSIS?
</div>
</div>


The main database in Server A has 700 tables... but there is a database in ServerB that will have only a subset of it. (75-100 tables)..

This list can vary from week to week or month to month.. and when there is a request to refresh the data, all the data of tables in the list has to be sent over with the replace.

if the list is modified and some tables are added and some removed.. that should be reflected in the next refresh run.

what tasks are needed to get this done in SSIS?

refresh ad-hoc table list from one server to another using SSIS?

Microsoft SQL Server 2008 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. Major improvements include the  Always On technologies and support for unstructured data types.

Microsoft SQL Server 2008

Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.

Microsoft SQL Server 2005

SQL Server Integration Services (SSIS) is a component of the Microsoft SQL Server database software that can be used to perform a broad range of data migration tasks. SSIS is a platform for data integration and workflow applications. It features a fast and flexible data warehousing tool used for data extraction, transformation, and loading (ETL). The tool may also be used to automate maintenance of SQL Server databases and updates to multidimensional cube data. SSIS replaced Data Transformation Services, which had been a feature of SQL Server since Version 7.0.