Hi,

Hi,

i created an SMO Transfer Object Script in SSIS using script task and would like to script out the T-SQL of the transfer to see exactly what it will run when i actually transfer the object (view in this case). However, how to i send the output of the ScriptTransfer() method to a file or to a screen so that i can view it?

here is my code:

Code:
Dim dbSourceName As String = "SMSPHdreb0f0"
Dim dbDestName As String = "SMSPHdreb0f0"

'Connect to the local, default instance of SQL Server.
Dim srvSource As Server
srvSource = New Server("dssdbs47")

Dim srvTarget As Server
srvTarget = New Server("dssdbs108")

'Reference the source database
Dim db As Database
db = srvSource.Databases(dbSourceName)

Dim dbCopy As Database
dbCopy = srvTarget.Databases(dbDestName)

'Define a Transfer object and set the required options.
Dim xfr As Transfer
xfr = New Transfer(db)
xfr.CopyAllTables = False
xfr.CopyAllDefaults = False
xfr.ObjectList.Add(db.Views("cd_desc_dcl", "dbo"))
xfr.CopyAllStoredProcedures = False
xfr.CopyAllDatabaseTriggers = False
xfr.CopyAllObjects = False
xfr.CopyAllPartitionFunctions = False
xfr.CopyAllPartitionSchemes = False
xfr.CopyAllRoles = False
xfr.CopyAllRules = False
xfr.CopyAllSchemas = False
xfr.CopyAllSqlAssemblies = False
xfr.CopyAllSynonyms = False
xfr.CopyAllUserDefinedAggregates = False
xfr.CopyAllUserDefinedDataTypes = False
xfr.CopyAllUserDefinedFunctions = False
xfr.CopyAllUserDefinedTypes = False
xfr.CopyAllUsers = False
xfr.CopyAllViews = False
xfr.DropDestinationObjectsFirst = True

xfr.Options.WithDependencies = False
xfr.Options.ContinueScriptingOnError = False
xfr.Options.ClusteredIndexes = False
xfr.Options.Indexes = False
xfr.Options.DriAllKeys = False
xfr.Options.DriForeignKeys = False
xfr.Options.DriPrimaryKey = False
xfr.Options.AllowSystemObjects = False
xfr.Options.PrimaryObject = False
xfr.Options.DriWithNoCheck = False
xfr.Options.NonClusteredIndexes = False
xfr.Options.OptimizerData = False
'xfr.Options.IncludeIfNotExists = true

xfr.DestinationDatabase = dbCopy.Name
xfr.DestinationServer = srvTarget.Name
xfr.DestinationLoginSecure = True

xfr.PrefetchObjects = False

xfr.CopySchema = True

'Include data
xfr.CopyData = True

'Execute the transfer
'xfr.TransferData()

'Script the transfer
xfr.ScriptTransfer()

Dts.TaskResult = ScriptResults.Success
if i comment out the xfr.TransferData(), it migrates the view successfully to my destination server. However, i want to see the actual code it is running but i don't know how to send the data to a file using ScriptTransfer().

thanks
scott