Transfer SQL Server Objects Task in SSIS

The Transfer SQL Server Objects task in SSIS is used to transfer one or more SQL Server objects (such as Tables, Views, Stored Procedures, User Defined Functions, Indexes, Users, Logins, etc.) from one Database to another Database. Transfer SQL Server Objects task in the Integration Services allows us to use the same instance for Source and Destination connections or 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 object or all the Server objects. Tables, Views, Stored Procedures, User Defined Functions, etc., are some of the 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 the Transfer Tables with Data article to understand how to Transfer Tables from Source to Destination.
  • Transfer SQL Server Objects Task in SSIS allows you to transfer Table structures, which means tables without data. Please refer to the Transfer Table Structures article to understand how to Transfer 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 to the Source Database. Please refer to the Transfer Stored Procedures 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 all User Defined Functions. Or it transfers all the User Defined Functions in the Source Database. Please refer to the Transfer User-Defined Functions article to know how to Transfer User Defined Functions from Source to Destination.
  • Transfer SQL Server Objects task in SSIS allows you to select all Views or transfer all Views. Please refer to the Transfer Views 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 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 this:

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

Objects Tab

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

Transfer SQL Server Objects Task in SSIS 3

Connection

  • SourceConnection: Configure the Connection Manager settings for the Source Database. If you have already created one, select from the list or click <New connection…> to create a New connection.
  • SourceDatabase: It is the database name 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 have already created it, select it from the list. Or else click <New connection…> to create a New connection.
  • DestinationDatabase: Select the name of a Database on the Destination Connection to which selected objects are 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 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: Copying data to the destination table gives you an option of whether to append or replace data. If you select the Append option, New data is added to old data. Otherwise, new data is replaced by old data.
  • CopySchema: Copy the schema of the objects from Source to Destination.
  • UseCollation: Each Database has its own Collation Settings. By setting this option to TRUE, the 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 also copy those dependent objects.

Destination Copy Objects

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

Here, we will explain the Server Objects available in the Transfer SQL Server Objects Task in SSIS for you.

  • CopyAllTables: If you want to transfer all the tables from the Source database to the Destination Database, set this option to TRUE. If you’re going to move one or a few tables, set FALSE and select the required tables from TablesList.
  • TablesList: Besides this option, there is a (…) button. Click on that button to show all the Tables present in the Source Database. Your job is to select the required tables from the list. These selected tables are Transferred to the Destination Database.
  • CopyAllViews: If you want to transfer all the Views from the Source database to the Destination Database, set this option to TRUE. If you’re going to transfer one or a few Views, set FALSE and select the required Views from ViewsList.
  • ViewsList: Besides this option, there is a (…) button. Click on that button to show 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 the 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 to show all the User Defined Stored Procedures in the Source Database, and your job is to select the required Stored Procedures from the list. These selected User Defined Stored Procedures are Transferred to the Destination Database.
  • CopyAllUserDefinedFunctions: If you want to transfer all the User Defined Functions from the Source database to the 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 the 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 are 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 a 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 are 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 Assemblies from the Source database to Destination Database, set this option to TRUE. To transfer one or few Assemblies then set FALSE and select the required Assemblies from it.
  • SqlAssembliesList: Besides this option, there is a (…) button. Click on that button will show all the 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 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.