Nisarg Upadhyay
View Connection and Database

Understanding Transfer database objects in SSDT 2017

March 2, 2022 by

In this article, we are going to learn how we can copy database objects between two databases of different instances of SQL Server. Sometimes, we receive the request to provide the specific database object to another database. To fulfill this requirement, instead of using Generating the T-SQL script for each object and export data using import-export task, we can use the Transfer SQL Server Object task of SSDT 2017. In this article, we will understand how we can transfer the database objects of a specific schema to another database.

Environment setup

I have created two named instances of SQL Server 2019 named SQL01 and SQL02 on my workstation. I have restored the wideworldimportors database on the SQL01 database. I have created an empty database named SalesDB on SQL02 instance. We want to transfer the tables of the Sales schema to the SalesDB database of SQL02. To do that, we are using the transfer SQL Server of the task of integration services.

Create an integration services project in SSDT 2017.

First, let us create an integration services project named transfer_sales_schema in SSDT 2017. Drag Transfer SQL Server Objects Task from SSIS toolbox and drop it on Control flow designer and rename it to Copy Sales Schema.

Drag and drop transfer database object task in SSDT 2017

Now, double click on the transfer SQL Server objects task. A dialog Transfer SQL Server objects task editor opens. Click on Source Connection and select the new connection. The SMO Connection Manager opens. Specify the source server name in the dialog box and specify the appropriate credential to connect to it. Click OK to save the source connection.

Create a source connection in SSDT 2017

Back to the Transfer database object task, click on Source Database and select Wideworldimportors from the list.

View Connection and Database

Similarly, click on Destination Connection and select New Connection. On the SMO connection manager, specify the destination server.

Create a destination connection in SSDT 2017

Back to the Transfer database object task, click on Source Database and select SalesDB from the list.

View destination database and connection

Choose the objects

We want to copy the schema and data of the Sales schema to the SalesDB database. So, we will set the following values in the transfer SQL Server database object tasks.

Destination Section

  1. Drop Objects First: We want to drop and create the tables, so I have set it to TRUE
  2. Copy Data: We are copying the data so, I have set it to TRUE

Configure the destination section of transfer SQL Server object task

Destination Copy Options

Copy All Objects: We are copying specific objects, so I have set their value to FALSE.

Copy All Tables: We are copying specific tables, so I have set it to False.

Tables List: We are copying the tables of Sales schema, so select them from the list.

Select tables of sales schema

Copy All Schema: We are copying the Sales Schema to set its value to FALSE.

Schema List: We want to copy the sales schema so, select Sales from the schema list.

Security Section:

  1. Copy Database Users: We are transferring the database users, so Set it to TRUE
  2. Copy Database Roles: We are transferring the database roles, so set it to TRUE
  3. Copy SQL Server Logins: We are transferring the SQL logins, so set it TRUE
  4. Copy Object Level permission: Set it to TRUE

Transfer security settings

Table Options:

  1. Copy Indexes: We are copying the indexes, so set it to TRUE
  2. Copy Triggers: We are copying the triggers, so set it to TRUE
  3. Copy Primary key: We are copying the Primary keys, so set it to TRUE
  4. Copy Foreign key: We are copying the foreign keys, so set it to TRUE

Transfer tables option

Click OK to save the configuration and close the dialog box. The SSIS package looks like the following image:

SSIS Package

Execute the package

Execute the SSIS package by clicking on the Start button from the menu bar.

Start the execution of SSIS package

The package execution begins. Once the package executes successfully, we will verify that tables have been.

Note: if you are transferring the data of the table that is in the secondary filegroup, then you must create that filegroup in the destination database. Similarly, if the table has a partition scheme and partition function, you must create them on the destination database.

Verify the schema

Now, let us verify whether tables have been copied to the destination server or not. Open SQL Server Management studio 🡪 Connect to Nisarg-PC\SQL02. Run the following queries.

Query to get the tables of sales schema.

Output

View tables of SalesDB

Query to get the list of primary keys

Output

View primary keys

As you can see, the primary keys have been created in the SalesDB database.

Query to get the list of indexes.

Output

View indexes

As you can see, the indexes have been created successfully.

Summary

In this article, we learn how to use the Transfer SQL Server object task to export the specific schema and the data from one database to another. To learn the method, we have exported a few tables from the Sales schema of the wideworldimportors database to the SalesDB database using the Transfer SQL Server object task of SSDT 2017.

Nisarg Upadhyay
Business Intelligence, ETL

About Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration. He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com

894 Views