SSIS Copy Table Data from one SQL Instance to Another

This SSIS article shows how to transfer or copy Table data from one SQL Server Instance to Another. To demonstrate this, we transfer four tables from Task Result Database to TrasferTasks.

The below Object Explorer screenshot shows the TrasferTasks Database Tables.

Object Explorer

SSIS Copy Table Data from one SQL Instance to Another

Drag and Drop the Transfer SQL Server Objects Task to the SSIS Control Flow Region. You can also use the Data Flow Task to copy tables from one SQL instance to another.

Transfer SQL Server Objects Task

Double-click on it to open the Editor. If you already have an existing connection, choose them. Otherwise, click the <New Connection> option.

Create a Source Connection

Provide the Server Name and Authentication. Next, select the Source Database.

Server Name and Authentication Mode

Create a new connection for the Destination Server.

SSIS Copy Table Data from one SQL Instance to Another 5

Choose the Destination Database from the available list.

Select the Destination Database

Select the Tables that you want to copy from one instance to another.

SSIS Copy Table Data from one SQL Instance to Another 7

Select True to copy Data along with the table structure.

Copy Data Along with Structure

Run the Copy Table Data from one SQL Server Instance to Another package.

Run SSIS Copy Table Data from one SQL Instance to Another Package

You can see the Tables in TrasferTasks Database within the SQL Management Studio.

SSIS Copy Table Data from one SQL Instance to Another 10