June 5, 2010 at 9:18 pm
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:
*************************************
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
June 6, 2010 at 7:21 pm
I'm not aware of a way to do this within the task itself. But you could trace it with profiler.
CEWII
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply