Post reply

SMO Transfer Object Task

  • June 5, 2010 at 9:18 pm

    #139356

    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

    #1176916

    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