SMO Transfer Object Task - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

SMO Transfer Object Task

Author

Topic

scabral7
Yak Posting Veteran

57 Posts

Posted - 2010-06-05 : 23:23:51

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

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2010-06-05 : 23:49:56

ScriptTransfer() returns a StringCollection. Just loop through the collection, dumping each element to your file.

elsasoft.org