Transfer SQL Server Objects Task in SSIS 2008 R2 With Example

Friends,

The Transfer SQL Server Objects task is used to transfer one or more SQL Server objects to a different database, either on the same or another SQL Server instance. This allows you to select different types of objects you want to transfer. You can select tables, views, stored procedures, user defined functions etc. Not only this, you can select a combination of these types of objects to transfer and even select particular objects of a particular object type.

Now let me demonstrate how you can create an SSIS package with the Transfer Database Task.

Go to START -> Microsoft SQL Server 2008 -> SQL Server Business Intelligence Development Studio to launch BIDS.

Then go to File menu -> New -> Project -> Select “Business Intelligence Projects” in the left tree pane -> Select “Integration Services Projects” and name the project as you wish and click OK.

In this new project you will see one package is already added with the name “Package.dtsx“. Drag the “Transfer SQL Server Objects Task” from the Toolbox (which is normally on the left side) to the Control Flow pane. Right click on the task and select Edit… as shown below.

In the “Transfer Sql Server Objects Task Editor”,click on Objects to set the different properties for this task. select Databases on the left and now you are ready to configure this task. Source Connection is the property to specify the connection for the source SQL Server instance, if you have already created a connection manager then you can reuse it here or can create a new one as shown below. This will also need to be done to configure the Destination Connection property as well.

In this scenario I selected source connection as KUSHI_HP (Server Name) and source database as AdventureworksDW database, For destination connection I selected as KUSHI_HP (Server Name) and Destination database as TEST

Source Database is the name of the database from where you are copying the objects and Destination Database is the name of the database to which you are copying the objects to.

• Connection
  • SourceConnection – the source instance
  • SourceDatabase – name of the source database
  • DestinationConnection – the destination instance
  • DestinationDatabase – name of the new database
• Destination
  • DropObjectsFirst – Drop selected objects on the target before copy
  • IncludeExtendedProperties – While copying operation also include extended properties of SQL objects being copied
  • CopyData – While copying tables, transfer the data of the selected tables as well
  • ExistingData – whether to append or replace data
  • CopySchema – Copy the schema of the objects being copied
  • UseCollation – Make sure collation of the columns are appropriately set on copied tables
  • IncludeDependentObjects – Include all the objects in copy operation which are dependent on selected objects
• Destination Copy Objects
  • CopyAllObjects – Do you want to copy all objects from the source database, if set to False, next property ObjectsToCopy will get enabled.
  • ObjectsToCopy – With this property you select types of objects you want to copy. You can select all objects of one or more types or select particular objects as you can see in the below image. Depending on the SQL Server version, type of objects selection will vary.
• Security
  • CopyDatabaseUsers – whether to include users
  • CopyDatabaseRoles – whether to include roles
  • CopySQLServerLogins – whether to include logins
  • CopyObjectLevelPermissions – whether to include object level permissions
• Table Options
  • CopyIndexes – whether to include indexes
  • CopyTriggers – whether to include triggers
  • CopyFullTextIndexes – whether to include full text indexes
  • CopyAllDRIObjects – whether to include referential integrity objects
  • CopyPrimaryKeys – whether to include primary keys
  • CopyForeignKeys – whether to include foreign keys

• GenerateScriptsInUnicode – whether to create script in Unicode or not

Here we have to  select the objects that we need to be migrated. A detail lists of objects are given in the editor as shown below. Assume, you need to copy two tables and two views.

In this scenario I selected two tables to transfer into TEST database.

Now you  also need to select few views hence, click on “collectionlist/browse button” in  “viewlist”. A pop up appears with the list of views, check the views that need to be copied.

click ok and then execute the task to copy all selected objects to destination database. You can also copy the data by setting the property “Copy Data” to true. To copy all objects in the database, you need not set each property, Instead you can set “CopyAllObjects” to true.

The person executing the package with this task must have at least browse objects permissions on the source database and on the destination database must have permissions to drop and create objects.

Thanks Archana for a well structured doc.

Regards,
Roopesh Babu V