Home > Copy Objects, SQL Server, SSIS, SSIS 2008, SSIS Tasks > Copy all objects from one database to another in SSIS using “Transfer SQL Server Objects Task”

Copy all objects from one database to another in SSIS using “Transfer SQL Server Objects Task”

sometimes, you may need to copy some/ all objects from one database to another.  well this can  easily done in SSIS using the task “Transfer SQL Server Objects Task“. The usage of this task is very simple. Drag the task from toolbar for control flow. Double click on the task to start using it.

task

The editor for the task opens. First thing that you need to do is to create the SMO connection managers for both source and destination SQL servers. This can be done by clicking on the drop down provided for source and destination server tabs as shown below.

filetask

Then select the source and destination database. You can then select the objects that need to be migrated. A detail lists of objects are given in the editor as shown below. Assume, you need to copy all the tables and two views.  To achive this, set the property “copy all table = true”.

task3

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.

task4

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 Datato true. To copy all objects in the database, you need not set each property, Instead you can set “CopyAllObjects” to true.

Advertisements
  1. Rohidas
    June 14, 2010 at 9:12 am

    Great But i want incremental transaction add in destination table like Replication
    so but this task is working good

  2. July 11, 2012 at 10:36 pm

    i dont want to copy my data just want to update the schema and bring the objects only not the data , the older data remains the same can it is possible with this..??

  3. March 5, 2014 at 6:26 pm

    I think the admin of this site is in fact working hard for his site, because here every stuff
    is quality based information.

  4. Johnson
    January 16, 2017 at 6:53 am

    fantastic post! This is so chock full of users information and the resources you provided was helpful to me. This is comprehensive and helpful list. There I found informative blog explaining Copy Data From One Server To Another In SQL Server which I Found interesting:http://www.sqlmvp.org/copy-data-from-one-server-to-another-server

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: