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.
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.
