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.

Connection
SourceConnection	AKMEHTA
SourceDatabase	Products
DestinationConnection	AKMEHTASQL2008
DestinationDatabase	Products
Destination
DropObjectsFirst	True
IncludeExtendedProperties	True
CopyData	True
ExistingData	Replace
CopySchema	True
UseCollation	True
IncludeDependentObjects	True
Destination Copy Options
CopyAllObjects	True (If you want to copy selected objects then this option should be False and make the choice of objects which needs to be copied in ObjectsToCopy)
ObjectsToCopy
Security
CopyDatabaseUsers	True
CopyDatabaseRoles	True
CopySQLServerLogins	False
CopyObjectLevelPermissions	True
Table Options
CopyIndexes	True
CopyTriggers	True
CopyFullTextIndexes	True
CopyAllDRIObjects	True
CopyPrimaryKeys	False
CopyForeignKeys	False
GenerateScriptsInUncode	False

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.