Copy data of each table from server A to server B dynamically using SSIS

Question

My task is to create workflow in SSIS where it will be copying data of each table from server A to the same tables in server B. For now, I have stopped in step where I'm taking data from A server and copy it to server B. Till now I have created workflow where steps are as below:

1. Read data from Excel file where there are placed names of tables to be processed
2. Insert this rows in destination database (server B) for future
3. In Control Flow connected above steps to next object - Execute SQL task where inside I'm taking all loaded names from table to global project variable named as "GlobalListOfTables".
4. Created another variable "localTable" where I'm storing each row of "GlobalListOfTables" variable/set which I'm using it later in "FOR LOOP".

Here I have stopped and what I want to do is:

5. Inside this LOOP get all data from source server of each table using created "localTable" variable and after insert this all data into destination server into the same table name as is in "localTable" variable.

This last point 5 makes me a little bit confused because tables are gonna be different from each other so there is no one pattern of tables struckture.

Can someone let me know how should I do in SSIS and which control should I take from available list to achieve point 5?

Answer 1

Transfer Database Task and Transfer SQL Server Objects Task

If you are looking to copy all data from a database to another you should refer to the Transfer Database Task and Transfer SQL Server Objects Task:

Based on the Transfer Database Task official documentation:

The Transfer Database task transfers a SQL Server database between two instances of SQL Server. In contrast to the other tasks that only transfer SQL Server objects by copying them, the Transfer Database task can either copy or move a database. This task can also be used to copy a database within the same server.

Also, based on Transfer SQL Server Objects Task official documentation:

The Transfer SQL Server Objects task transfers one or more types of objects in a SQL Server database between instances of SQL Server. For example, the task can copy tables and stored procedures. Depending on the version of SQL Server that is used as a source, different types of objects are available to copy. For example, only a SQL Server database includes schemas and user-defined aggregates.

Detailed articles

There are many article describing how to work with these tasks and all available options, as example:

• Transfer Database Task and Transfer SQL Server Objects Task in SSIS
• Copy all objects from one database to another in SSIS using “Transfer SQL Server Objects Task”