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.


Continues…

Pages: 1 2 3




Related Articles :

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |