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 same SQL Server instance for Source and Destination connections or you can use different instances.

Some of the important 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 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 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 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 will transfer all the User Defined Functions in the Source Database. Please refer Transfer SQL Server User Defined Functions in SSIS article to understand, 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 will transfer all Views. Please refer Transfer SQL Server Views in SSIS article to understand, 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 Transfer SQL Server Objects Task name and provide the description. In real-time please change the Task name to appropriate name and write the description to explain the task functionality because it will help other developers to understand the task better.

Transfer SQL Server Objects Task in SSIS 2

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.

Objects Tab

In this tab we are going to perform all the Transfer SQL Server Objects Task operations. Lets 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 then select from the list or else click <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 then 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 will be 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: Specify whether you want to drop the selected objects on the destination Database before copy them.
  • 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 row data of the selected tables as well? If yes then select TRUE. If you select FALSE then empty tables with column names will be transferred.
  • ExistingData: While copying data to the destination table, it gives you an option whether to append or replace data. If you select Append option then New data will be added to old data otherwise, new data will be 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 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 then, by setting this option 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 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 then set this option to TRUE. If you want to transfer one or few tables then 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 will be Transferred to the Destination Database.
  • CopyAllViews: If you want to transfer all the Views from the Source database to Destination Database then set this option to TRUE. If you want to transfer one or few Views then set FALSE and select the required Views from ViewsList
  • ViewsList: Besides this option there is a (…) button. Click on that button will 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 be Transferred to the Destination Database.
  • CopyAllStoredProcedures: If you want to transfer all the User Defined Stored Procedures from the Source database to Destination Database then set this option to TRUE. If you want to transfer one or few Stored Procedures then 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 will be Transferred to the Destination Database.
  • CopyAllUserDefinedFunctions: If you want to transfer all the User Defined Functions from the Source database to Destination Database then set this option to TRUE. If you want to transfer one or few User Defined Functions then 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 selected User Defined Functions will be Transferred to the Destination Database.
  • CopyAllDefaults: If you want to transfer all the Defaults from the Source database to Destination Database then set this option to TRUE. If you want to transfer one or few Defaults then set FALSE and select the required ones from DefaultsList
  • DefaultsList: Besides this option there is a (…) button. Click on that button will show all the Defaults present in the Source Database and your job is to select the required Defaults from the list. These selected Defaults will be Transferred to the Destination Database.
  • CopyAllUserDefinedDataTypes: If you want to transfer all the User Defined Data Types from the Source database to Destination Database then set this option to TRUE. If you want to transfer one or few Data Types then set FALSE and select 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 select the required User Defined Data Types from the list. These selected Data Types will be Transferred to the Destination Database.
  • CopyAllPartitionFunctions: If you want to transfer all the Partition Functions from the Source database to Destination Database then set this option to TRUE. If you want to transfer one or few Partition Functions then set FALSE and select the required Partition Functions from PartitionFunctionsList
  • PartitionFunctionsList: Besides this option there is a (…) button. Click on that button will show all the Partition Functions present in the Source Database and your job is to select the required Partitions from the list. These selected ones will be Transferred to the Destination Database.
  • CopyAllPartitionSchemes: If you want to transfer all the Partition Schemes from the Source database to Destination Database then set this option to TRUE. If you want to transfer one or few Partition Schemes then set FALSE and select the required Partition Schemes from PartitionSchemesList
  • PartitionSchemesList: Besides this option there is a (…) button. Click on that button will show all the Partition Schemes present in the Source Database and your job is to select the required Partitions from the list. These selected ones will be Transferred to the Destination Database.
  • CopyAllSchemas: If you want to transfer all the Database Schemas from the Source database to Destination Database then set this option to TRUE. If you want 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 will be Transferred to the Destination Database.
  • CopyAllSqlAssemblies: If you want to transfer all the SQL Assemblies from the Source database to Destination Database then set this option to TRUE. If you want 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 will be Transferred to the Destination Database.
  • CopyAllUserDefinedAggregates: If you want to transfer all the User Defined Aggregates from the Source database to Destination Database then set this option to TRUE. If you want to transfer one or few Aggregates then set FALSE and select the required ones from UserDefinedAggregatesList
  • UserDefinedAggregatesList: Besides this option there is a (…) button. Click on that button will show all the User Defined Aggregates present in the Source Database. And your job is to select the required Aggregates from the list. These selected ones will be Transferred to the Destination Database.
  • CopyAllUserDefinedTypes: If you want to transfer all the User Defined Types from the Source database to Destination Database then set this option to TRUE. If you want to transfer one or few Types then set FALSE and select 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 selected ones will be Transferred to the Destination Database.
  • CopyAllXmlSchemaCollections: If you want to transfer all the XML Schema Collections from the Source database to Destination Database then set this option to TRUE. If you want to transfer one or few XML Schema then set FALSE and select 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 will be Transferred 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: Specify 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 permissions to drop and create objects.

Thank you for Visiting Our Blog