Tutorial Gateway

  • C
  • C#
  • Java
  • Python
  • SQL
  • MySQL
  • Js
  • BI Tools
    • Informatica
    • Talend
    • Tableau
    • Power BI
    • SSIS
    • SSRS
    • SSAS
    • MDX
    • R Tutorial
    • Alteryx
    • QlikView
  • More
    • C Programs
    • C++ Programs
    • Go Programs
    • Python Programs
    • Java Programs

Transfer SQL Server Objects Task in SSIS

by suresh

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 in SSIS.
  • 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.

Placed Under: SSIS

  • What is SSIS
  • Install SQL Server Data Tools
  • Learn SSIS in 28 Days
  • Create a SSIS Project
  • Create SSIS Package
  • SSIS Connection Manager
  • OLE DB Connection Manager
  • SSIS ADO Connection Manager
  • ADO.NET Connection Manager
  • SSIS Cache Connection Manager
  • SSIS Excel Connection Manager
  • SSIS File Connection Manager
  • SSIS FTP Connection Manager
  • SSIS SMO Connection Manager
  • Source Assistance in SSIS
  • Flat File Source in SSIS
  • OLE DB Source in SSIS
  • Excel Source in SSIS
  • ADO.NET Source in SSIS
  • SSIS FLAT FILE Destination
  • SSIS OLE DB Destination
  • SSIS ADO.NET Destination
  • SSIS Transformations
  • SSIS Audit Transformation
  • SSIS Aggregate Transformation
  • SSIS Aggregate Advanced Mode
  • SSIS Cache Transformation
  • SSIS Character Map
  • SSIS Conditional Split
  • SSIS Copy Column
  • SSIS Data Conversion
  • SSIS Derived Column
  • SSIS Export Column
  • SSIS Fuzzy Grouping
  • SSIS Fuzzy Lookup
  • SSIS Import Column
  • SSIS Lookup Introduction
  • Lookup – OLE DB Connection
  • SSIS Lookup in Full Cache Mode
  • SSIS Lookup – Case Sensitivity
  • SSIS Merge Transformation
  • SSIS Merge Join Transformation
  • SSIS Merge Join – Left Outer Join
  • SSIS Merge – Right Outer Join
  • SSIS Merge Join – Full Outer Join
  • SSIS Multicast Transformation
  • SSIS OLEDB Command
  • OLEDB Command – Delete Data
  • OLEDB Command-Update Data
  • SSIS Percentage Sampling
  • SSIS Pivot Transformation 2008
  • SSIS Pivot Transformation
  • SSIS Row Count Transformation
  • SSIS Row Sampling
  • Script Component as Source
  • Script Component as Destination
  • SSIS Script as Transformation
  • SSIS Sort Transformation
  • SSIS SCD Type 0
  • SSIS SCD Type 1
  • SSIS SCD Type 2
  • SSIS Term Lookup
  • SSIS Term Extraction Intro
  • Term Extraction – Extract Nouns
  • SSIS Extract Noun Phrases
  • Extract Nouns & Noun Phrases
  • Term Extraction – Exclusion Tab
  • SSIS Unpivot Transformation
  • SSIS Union All Transformation
  • SSIS For Loop Container
  • SSIS ForEach File Enumerator
  • SSIS ForEach SMO Enumerator
  • SSIS ForEach Variable
  • SSIS Foreach NodeList
  • Foreach ADO.NET Schema Rowset Enumerator
  • SSIS Bulk Insert Task
  • SSIS Data Profiling Task
  • Execute T-SQL Statement Task
  • SSIS Execute SQL Task Intro
  • SSIS Execute SQL Task Example
  • Execute SQL Task- Single Rowset
  • Execute SQL Task – Full Row Set
  • SSIS Execute Package Task
  • Execute Packages in SQL Server
  • Execute Packages in File System
  • SSIS Execute Package Project Reference
  • SSIS File System Task
  • SSIS File System Task- Copy Files
  • File System Task-Copy Directory
  • File System Task – Delete Files
  • File System Task – Delete Folder
  • File System Task -Move Directory
  • SSIS File System Task -Move File
  • File System – Move Multiple files
  • File System Task – Rename File
  • File System Task – Set Attributes
  • SSIS FTP TASK
  • SSIS Create Local Directory
  • SSIS Create Remote Directory
  • SSIS FTP Task Send Files
  • SSIS FTP – Send Multiple Files
  • SSIS FTP Task Delete Local Files
  • FTP TASK Delete Local Directory
  • FTP Task Delete Remote files
  • SSIS Delete Remote Directory
  • SSIS FTP Task Receive Files
  • SSIS FTP Receive Multiple Files
  • SSIS Script Task
  • Transfer SQL Server Objects Task
  • Transfer SQL Table Structures
  • Transfer SQL Tables with Data
  • Transfer SQL Stored Procedures
  • Transfer User Defined Functions
  • Transfer SQL Views in SSIS
  • SSIS Web Service Task
  • SSIS XML Task-Validate XML File
  • Transform XML File using XSLT
  • XML Task-XML files Differences
  • Create SSIS Catalog
  • Package Deployment using BIDS
  • Deploy Package Using SQL
  • Deploy using SQL Server Wizard
  • SSIS Breakpoints
  • SSIS Checkpoints
  • SSIS Error Handling
  • SSIS Event Handlers
  • SSIS Transactions
  • SSIS Logging
  • SSIS Parameters
  • SSIS Package Configuration
  • Configure using SQL Server
  • Config using Registry Entry
  • Conf with Environment Variable
  • SSIS XML Configuration File
  • XML Configuration File Part 2
  • SSIS Package Protection Level
  • SSIS Incremental Load
  • Incremental Load Example 2
  • SSIS Remove Double Quotes

Copyright © 2021 · All Rights Reserved by Suresh

About Us | Contact Us | Privacy Policy