How to copy a table from a database to an other using SSIS (SQL Server Integration Services)

SQL Server Integration Services Transfer SQL Server Objects Task Control Flow item can be used to transfer a table as well as any database object in SQL Server between SQL Server instances.

Transfer SQL Server Objects Task

You can also transfer table related objects like primary keys, foreign keys, indexes, full text indexes and triggers as well. You can also indicate whether the data within the table will be transferred by setting the CopyData property of the "Transfer SQL Server Objects Task" control item in the Transfer SQL Server Objects Task Editor window. You have two choices if you decided to copy the data to the destination. You can replace the existing data in the destination table by selecting the "Replace" option. Or you can select "Append" to add the data from source tables to the destination tables.

Here is the sample where a table is copied from a database to an other database within the same instance. The transferred table previously is not in the target database.

Create a new SSIS package within the Integration Services Project and name it as MoveTable.dtsx

In the Control Flow layout drag and drop the Transfer SQL Server Objects Task from the toolbox. You can display the toolbox for the SSIS layout by using the Ctrl+Alt+X key combination.

SSIS Control Flow layout

Double click the Transfer SQL Server Objects Task item in the layout to open the Transfer SQL Server Objects Task Editor dialog screen where you can configure the object and set its properties.

Transfer SQL Server Objects Task Editor

In the General tab you can set the name of the object and add a descriptive information about the object in short.

In the Objects tab you can configure more detail information with related to the task details and configuration.

transfer SQL Server objects from one database to another

Connection properties
SourceConnection and SourceDatabase is the place for the source objects for our sample where the table to be transferred exists.
DestinationConnection and DestinationDatabase is the target SQL Server and database where we plan to transfer the database objects to.

Destination properties
DropObjectFirst identifies whether in the destination database the transferred objects will be dropped as the first step. Since for our case the object to be transferred is not currently in the target database, we no need to run a drop procedure. So set it to False.
Set the IncludeExtendedProperties to True.
Since we want to transfer the data too with the object structure set the CopyData property to True
ExistingData parameter defines whether the data will be replaced or appended to the existing database tables.
CopySchema property controls table creation during data transfer process. If CopySchema is True, transfer process creates related tables in the destination database prior to copying data. If CopySchema is left as False, all related tables in the transfer process must exist in the destination database.

Destination Copy Options
Destination Copy Options section is used to set the objects to be transferred. You can copy all tables by setting CopyAllTables property to True. If you want to transfer or copy a set of tables instead of all the tables in the source database, you can use the TablesList collection to include the desired tables in the process task.

Table Options
In the Table Options section we can set whether the indexes on the source table will also be transferred by setting the CopyIndexes property to True or False. CopyTriggers, CopyFullTextIndexes, CopyPrimaryKeys, CopyForeingKeys can be also configured for similar aims. For our sample case, along with the table, the indexes, primary keys and the foreign keys also be transferred. So set the related property values to True.

Let the sample table be named as TimeZones in the dbo schema. If CopySchema property is left as False, you may have the below error message since in the target database the related table TimeZones does not exist.

[Transfer SQL Server Objects Task] Error: Execution failed with the following error: "ERROR : errorCode=-1073548784 description=Executing the query "SET IDENTITY_INSERT [dbo].[TimeZones] ON" failed with the following error: "Cannot find the object "dbo.TimeZones" because it does not exist or you do not have permissions.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}".

To execute the package, right click on the package and select Execute Package on the context menu in the solution explorer.

execute SQL Server Integration Services package

You can see the results of the MoveTable.dtsx package execution on the "Execution Results" tab.