Tutorial Gateway

Transfer SQL Server Objects Task in SSIS

by

The Transfer SQL Server Objects task in SSIS is used to transfer one or more SQL Server objects (such as Tables, View, Stored Procedures, User Defined Functions, Indexes, Users, Logins, etc.) from one Database to another Database. Transfer SQL Server Objects task in SQL Server Integration Services allows us to use either the same SQL Server instance for Source and Destination connections or use different instances.

Some of the critical features of the Transfer SQL Server Objects task in SSIS are:

  • Transfer SQL Server Objects task in SSIS allows you to select either one type of SQL object or all the SQL Server objects. Tables, View, Stored Procedures, User Defined Functions, etc., are some of the SQL Server Objects.
  • Transfer SQL Server Objects task in SSIS allows you to select either all tables, or it will transfer all the Tables. Please refer to Transfer SQL Server Tables with Data in SSIS article to understand, How to Transfer SQL Server Tables from Source to Destination.
  • Transfer SQL Server Objects Task in SSIS allows you to transfer SQL Server Table structures means table without data. Please refer to Transfer SQL Server Table Structures in SSIS article to understand, How to Transfer SQL Server Table Structures from Source to Destination.
  • Transfer SQL Server Objects task in SSIS allows you to select either all Stored Procedures. Or it will transfer all the Stored Procedures in the Source Database. Please refer to Transfer SQL Server Stored Procedures in SSIS article to understand, How to Transfer User Defined Stored Procedures from Source to Destination.
  • Transfer SQL Server Objects task in SSIS allows you to select either all User Defined Functions. Or it transfers all the User Defined Functions in the Source Database. Please refer to Transfer SQL Server User-Defined Functions in SSIS article to know how to Transfer User Defined Functions from Source to Destination.
  • Transfer SQL Server Objects task in SSIS allows you to select either all Views. Or it transfers all Views. Please refer to Transfer SQL Server Views in SSIS article to know how to Transfer Views from Source to Destination.

Configuring Transfer SQL Server Objects Task in SSIS

Drag the Transfer SQL Server Objects Task from the Toolbox to the Control Flow Region. Double click on the task will open the Transfer SQL Server Objects Task Editor as shown below to configure it.

Transfer SQL Server Objects Task in SSIS 1

General Tab

This tab is purely used to change the SSIS Transfer SQL Server Objects Task name and provide the description. In real-time, please change the Task name to the appropriate name and write the description to explain the task functionality. It helps other developers to understand the task better.

For instance, if you are transferring tables, then write something like:

  • Name: Transferring SQL Server Tables
  • Description: Transferring SQL Server Tables called Employees and Sales with data from [Adventure Works 2014] to [SSIS Tutorials] Database.
Transfer SQL Server Objects Task in SSIS 2

Objects Tab

In this tab, we are going to perform all the Transfer SQL Server Objects Task operations in SSIS. Let’s explore them by sections.

Transfer SQL Server Objects Task in SSIS 3

Connection

  • SourceConnection: Configure the Connection Manager settings for the Source Database. If you already created, select from the list or else clicked <New connection…> to create New connection.
  • SourceDatabase: It is the name of the database from where you want to copy the objects like tables, views, and functions.
  • DestinationConnection: Configure the Connection Manager settings for the Destination Database. If you already created, select from the list. Or else click <New connection…> to create New connection.
  • DestinationDatabase: Select the name of a Database on the Destination Connection to which selected objects copied.

NOTE: The Transfer SQL Server Objects Task in SSIS supports SMO Connection Manager only so, For both Source and destination, create SMO connection Manager.

Destination

  • DropObjectsFirst: Before copying them, specify whether you want to drop the selected objects on the destination Database.
  • IncludeExtendedProperties: Specify whether you want to include extended properties of SQL objects while copying data.
  • CopyData: While copying tables, Do you want to transfer the data of the selected tables as well? If yes, then choose TRUE. If you choose FALSE, empty tables with column names will transfer.
  • ExistingData: While copying data to the destination table, it gives you an option of whether to append or replace data. If you select the Append option, New data added to old data. Otherwise, new data replaced by old data.
  • CopySchema: Copy the schema of the SQL objects from Source to Destination.
  • UseCollation: Each Database has its own Collation Settings. By setting this option to TRUE, SSIS Transfer SQL Server Objects Task will use the collation setting of the Source database in the Destination Database.
  • IncludeDependentObjects: If there are any dependent Objects, by setting this option, it will copy those depended objects as well.

Destination Copy Objects

  • CopyAllObjects: Do you want to copy all the SQL objects from the source database to the Destination Database. If you set this to TRUE, then all the SQL objects will be transferred at one Go. If set to False, next property ObjectsToCopy will get enabled.
  • ObjectsToCopy: By expanding this property, you can select the individual SQL objects from the Source Database. These Object types may vary depending on the SQL Server version. Below screenshot will show you the ones
Transfer SQL Server Objects Task in SSIS 4

Here we are going to explain SQL Server Objects available in Transfer SQL Server Objects Task in SSIS for you

  • CopyAllTables: If you want to transfer all the tables from the Source database to Destination Database, set this option to TRUE. If you’re going to move one or few tables, set FALSE and select the required tables from TablesList
  • TablesList: Besides this option, there is a (…) button. Click on that button will show all the Tables present in the Source Database. And your job is to select the required tables from the list. These selected tables Transferred to the Destination Database.
  • CopyAllViews: If you want to transfer all the Views from the Source database to Destination Database, set this option to TRUE. If you’re going to transfer one or few Views, set FALSE and select the required Views from ViewsList
  • ViewsList: Besides this option, there is a (…) button. Click on that button shows all the Views present in the Source Database. Here your job is to select the required Views from the list. These selected Views will Transfer to the Destination Database.
  • CopyAllStoredProcedures: If you want to transfer all the User Defined Stored Procedures from the Source database to Destination Database, set this option to TRUE. To transfer one or few Stored Procedures, set FALSE and select the required User Defined Stored Procedures from StoredProceduresList
  • StoredProceduresList: Besides this option, there is a (…) button. Click on that button will show all the User Defined Stored Procedures present in the Source Database, and your job is to select the required Stored Procedures from the list. These selected User Defined Stored Procedures Transferred to the Destination Database.
  • CopyAllUserDefinedFunctions: If you want to transfer all the User Defined Functions from the Source database to Destination Database, set this option to TRUE. To transfer one or few User Defined Functions, set FALSE and select the required User Defined Functions from UserDefinedFunctionsList
  • UserDefinedFunctionsList: Besides this option, there is a (…) button. Click on that button will show all the User Defined Functions present in the Source Database, and your job is to select the required User Defined Functions from the list. These User Defined Functions moved to the Destination Database.
  • CopyAllDefaults: If you want to transfer all the Defaults from the Source database to Destination Database, set this option to TRUE. To move one or few Defaults, set FALSE and pick the required ones from DefaultsList
  • DefaultsList: Besides this, there is a (…) button. Click on that button will show all the Defaults present in the Source Database, and your job is to choose the required Defaults from the list. These selected Defaults moved to the Destination Database.
  • CopyAllUserDefinedDataTypes: If you want to transfer all the User Defined Data Types from the Source database to Destination Database, set this option to TRUE. If you’re going to move one or few Data Types, set FALSE and pick the required User Defined Data Types from UserDefinedDataTypesList
  • UserDefinedDataTypesList: Besides this option, there is a (…) button. Click on that button will show all the User Defined Data Types present in the Source Database, and your job is to pick the required User Defined Data Types from the list. These selected Data Types Transferred to the Destination Database.
  • CopyAllPartitionFunctions: If you want to transfer all the Partition Functions from the Source database to Destination Database, set this option to TRUE. To shift one or few Partition Functions, set FALSE and choose the required Partition Functions from PartitionFunctionsList
  • PartitionFunctionsList: Besides this option, there is a (…) button. Click on that button displays all the Partition Functions present in the Source Database. Your job is to select the needed Partitions from the list. These selected ones will Transfer.
  • CopyAllPartitionSchemes: If you want to transfer all the Partition Schemes from the Source database to Target Database, set this option to TRUE. To move one or few Partition Schemes, set FALSE and select the required Partition Schemes from PartitionSchemesList
  • PartitionSchemesList: Besides this, there is a (…) button. Click on that button shows all the Partition Schemes present in the Source Database, and your job is to select the required Partitions from the list. These selected ones Transferred to the Destination Database.
  • CopyAllSchemas: If you want to transfer all the Database Schemas from the Source database to Destination Database, set this option to TRUE. To transfer one or few Schemas, then set FALSE and select the required Schemas from SchemasList
  • SchemasList: Besides this option, there is a (…) button. Click on that button will show all the Schemas present in the Source Database. Here your job is to select the required Schemas from the list. These selected ones Transferred to the Destination Database.
  • CopyAllSqlAssemblies: If you want to transfer all the SQL Assemblies from the Source database to Destination Database, set this option to TRUE. To transfer one or few SQL Assemblies then set FALSE and select the required SQL Assemblies from SqlAssembliesList
  • SqlAssembliesList: Besides this option, there is a (…) button. Click on that button will show all the SQL Assemblies present in the Source Database, and your job is to select the required Assemblies from the list. These selected ones Transferred to the Destination Database.
  • CopyAllUserDefinedAggregates: If you want to transfer all the User Defined Aggregates from the Source database to Destination Database, set this option to TRUE. If you’re going to move one or few Aggregates, set FALSE & select the required ones from UserDefinedAggregatesList
  • UserDefinedAggregatesList: Besides this option, there is a (…) button. Click on that button shows all the User Defined Aggregates present in the Source Database. And your job is to select the required Aggregates from the list. These chosen ones moved to the Destination Database.
  • CopyAllUserDefinedTypes: If you want to transfer all the User Defined Types from the Source database to Destination Database, set this option to TRUE. To move one or few Types, set FALSE and choose the required ones from UserDefinedTypesList
  • UserDefinedTypesList: Besides this option, there is a (…) button. Click on that button will show all the User Defined Types present in the Source Database. Here your job is to select the required Types from the list. These chosen ones will Transfer to the Target Database.
  • CopyAllXmlSchemaCollections: If you want to transfer all the XML Schema Collections from the Source database to Destination Database, set this option to TRUE. To move one or few XML Schema, set FALSE and choose the required ones from XmlSchemaCollectionsList
  • XmlSchemaCollectionsList: Besides this option, there is a (…) button. Click on that button will show all the XML Schema Collections present in the Source Database. And your job is to select the required XML Schemas from the list. These selected ones moved to the Destination Database.

Security

  • CopyDatabaseUsers: Specify whether you want to transfer Database users from Source to Destination Database by toggling between True and False.
  • CopyDatabaseRoles: Specify whether you want to transfer Roles from Source to Destination Database.
  • CopySQLServerLogins: Specify whether you want to transfer SQL Server Logins from Source to Destination Database.
  • CopyObjectLevelPermissions: Specify whether you want to transfer Object Level Permissions from Source to Destination Database.

Table Options

  • CopyIndexes: Specify whether you want to Copy the Table Indexes from Source to Destination Database by toggling between True and False.
  • CopyTriggers: Specify whether you want to Copy the Triggers on the tables from Source to Destination Database
  • CopyFullTextIndexes: Specify whether you want to Copy the full text indexes from Source to Destination Database
  • CopyAllDRIObjects: Specify whether you want to Copy the referral integrity on tables from Source to Destination Database
  • CopyPrimaryKeys: Specify whether you want to Copy the Primary Keys on the Tables from Source to Destination Database
  • CopyForeignKeys: Whether you want to Copy the Foreign Keys on tables from Source to Destination Database whether to include foreign keys
  • GenerateScriptsInUnicode: Specify whether you want to Generate the Script in Unicode Format or not

NOTE: The person executing the package holding Transfer SQL Server Objects Task in SSIS task must have permission to drop and create objects.