Using Transfer SQL Server Objects Task and Execute SQL Tasks in SSIS

7.       Double click the Transfer SQL Server Objects Task to open up the Transfer SQL Server Objects Task Editor.

In the Transfer SQL Server Objects Task Editor, you need to configure a SourceConnection and a DestinationConnection by selecting the <New connection…> option from the drop down list. This will open up the SMO Connection Manger Editor as shown in the below snippet, from there you need to set the SQL Server Instance name and the connection parameters. Click OK to save the SMO Connection Manager Editor.

Once you have established the SourceConnection and the DestinationConnection choose the appropriate Source and Destination databases. In this example the Source and Destination database names will both be Products. Next, you need to configure the Destination, Destination Copy Options, Security and Table Options as shown in the below table.

Finally, the Transfer SQL Server Objects Task Editor should look similar to the snippet shown below. Click OK to save the Transfer SQL Server Objects Task Editor Configuration.

8.       Connect the Execute SQL Task and the Transfer SQL Server Objects Task together as shown in the snippet below.