• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 332
  • Last Modified:

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

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?
1 Solution
How are you adding the tables. If it is a direct import or any such sort of thing using SSIS, you can create a reference table which has the information of all the tables that needs to be refereshed. Now build you query using this reference table to sync the tables required.
25112Author Commented:
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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now