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.
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.
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”.
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.
Great But i want incremental transaction add in destination table like Replication
so but this task is working good
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..??
I think the admin of this site is in fact working hard for his site, because here every stuff
is quality based information.
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