Transfer SQL Server objects task in SSIS

Hello Friends, I am back with one more SSIS topic i.e. “Transfer SQL Server objects task”. Using this task we can transfer the SQL Server objects from one server to another server. Let’s see how we can use “Transfer SQL Server objects task” in our SSIS package.

First of all we will take “Transfer SQL Server objects task” in control flow region (As shown below).

To configure the “Transfer SQL Server objects task”, we will right click on it and click on “Edit” menu that will bring a new screen in front of us(As shown in the below screenshot).

In the above screenshot, I have highlighted the connection section where we have to define the source and destination connection. In the destination section , there are several fields which I would like to explain.

Drop Objects First:- By default this field’s value is false, If we set the value of this field “True”, a check will be performed if the object that is being transferred already exists on the destination server will be dropped first before transferring the object from source to destination.

Include extended properties:- By default this field’s value is false, If we set the value of this field “True”, extended properties will be included when objects are copied from the source to the destination server.

Copy Data:- If copy data property is set to “True”, along with structure data will be copied to the destination server.

Copy Schema :- This property should always be true.

Use Collation :- If use collation property is set to “True”, source server will include the collation of source database while transferring the objects.

Include dependent objects:- If this property is set to “True”, all the dependent objects will also be transferred.

Copy all objects:- If we set this property true, all the object from the source database will be transferred to the destination database.

Similarly there are lots of properties which you can explore with yourself. I am going to transfer only one table from source that will be transferred to the destination server.

To select a particular table, we will click on the button(encircled with red color) that will bring a new screen containing the list of all user tables in the source database.

I have selected only one table “OLE DB Destination” that will be transferred from source database to destination database. We will click on “OK” button to close the current screen and click further “OK” to close all the screens.

Now we are ready with our package, lets execute it by pressing “F5” key and see the results.

Green color of task indicates that our package executed successfully. I hope you will find this article useful. Please feel free to ask any question related to this article.