This SSIS article shows how to copy or transfer SQL Server tables from one instance to another using a data flow task with an example.
To demonstrate this, we transfer two tables (Union All and Unpivot) from the Task Result Database to TrasferTasks. The below screenshot shows the Object Explorer.
SSIS Data Flow Task to Copy Tables from one SQL Instance to Another
Drag and drop the Data Flow Task into the control flow region.
Double-click to open the SSIS Data Flow Region. Then, drag the OLE DB Source and double-click on it to open the Editor. Next, click the New button to configure the OLE DB Connection Manager. Here, we have chosen the existing one.
- You can also try the Transfer SQL Server Objects Task to copy Tables from one instance to another – Copy Table Data from one SQL Instance to Another.
We will choose the existing Union ALL table from the list in this example. Next, go to the columns tab to check the columns and click the Ok button.
Drag another OLE DB Source and choose Unpivot Table.
Next, drag two OLE DB Destinations and connect them to the Sources.
Double-click on the first Destination to open the Editor. Then, we have chosen the existing connection.
Next, click the New button to create a new table.
Go to the Mappings tab to check the input and available destination column mapping.
Do the same procedure was for the second OLE DB destination.
Run the Data Flow Task to Copy Tables from one SQL Instance to Another package.
As you can see from the SQL Management Studio, both tables successfully transferred to the TrasferTasks Database.
