Create and populate a table remotely using SMO

A love using the SQL Server Management Objects (SMO) , one of the great advantages is been able to create sql server objects and execute sql scripts remotely. One good example is when a table schema is needed from a Source to a Destination, this task can be accomplish very easy using SSIS, but, where is the challenge doing that. So I create create an SMO script that will do just that. Now that's a challenge:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Common
Imports System.Text


<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

    Enum ScriptResults
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum

    ' The execution engine calls this method when the task executes.
    ' To access the object model, use the Dts object. Connections, variables, events,
    ' and logging features are available as static members of the Dts class.
    ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
    '
    ' To open Code and Text Editor Help, press F1.
    ' To open Object Browser, press Ctrl+Alt+J.
    Enum ScriptResults
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum

    Public Sub Main()


        Dim SourceServerName As String = Dts.Variables("User::str_SourceServer").Value.ToString
        Dim SourceDB As String = Dts.Variables("User::str_SourceDatabase").Value.ToString
        Dim SourceSchema As String = Dts.Variables("User::str_SourceSchema").Value.ToString
        Dim SourceTable As String = Dts.Variables("User::str_SourceTable").Value.ToString
        Dim SourceTable1 As String = SourceTable

        Dim DestServerName As String = Dts.Variables("User::str_DestinationServer").Value.ToString
        Dim DestDB As String = Dts.Variables("User::str_DestinationDatabase").Value.ToString
        Dim DestSchema As String = Dts.Variables("User::str_DestinationSchema").Value.ToString
        Dim DestTable As String = Dts.Variables("User::str_DestinationTable").Value.ToString
        Dim DestTable1 As String = DestTable
        Dim TableScript As Scripter
        Dim objectsToCopy As System.Collections.ArrayList
        Dim TableToCopy As String = SourceTable.ToLower()
        Dim DestinationTableName As String = DestTable.ToLower()


        Dim SourceServer As Server
        Dim SourceSrvConn As New ServerConnection

        Dim SourceConnStr As String = "Server=" + SourceServerName + ";Database=" + SourceDB + ";Trusted_Connection=True;"
        Dim TableScripts As System.Collections.Specialized.StringCollection
        Dim CriptingOpt As ScriptingOptions = New ScriptingOptions
        Dim DestConnStr As String = "Server=" + DestServerName + ";Database=" + DestDB + ";Trusted_Connection=True;"
        Dim col As System.Collections.Specialized.StringEnumerator




        SourceTable = SourceSchema + "." + SourceTable
        DestTable = DestSchema + "." + DestTable



        'Set Servers

        SourceSrvConn.ConnectionString = SourceConnStr
        SourceServer = New Server(SourceSrvConn)
        CriptingOpt.WithDependencies = False
        CriptingOpt.SchemaQualifyForeignKeysReferences = False
        'CriptingOpt.NoTablePartitioningSchemes = True
        CriptingOpt.NoFileGroup = True
        CriptingOpt.IncludeIfNotExists = True
        CriptingOpt.Triggers = False
        CriptingOpt.AllowSystemObjects = False
        CriptingOpt.AnsiPadding = False
        CriptingOpt.ClusteredIndexes = False
        CriptingOpt.ConvertUserDefinedDataTypesToBaseType = False
        CriptingOpt.DriAllConstraints = False
        CriptingOpt.DriAllKeys = False
        CriptingOpt.DriChecks = False
        CriptingOpt.DriForeignKeys = False
        CriptingOpt.EnforceScriptingOptions = True
        CriptingOpt.NoCollation = False
        CriptingOpt.DriPrimaryKey = True



        Dim DestinationServer As Server
        Dim DestinationConn As New ServerConnection
        DestinationConn.ConnectionString = DestConnStr
        DestinationServer = New Server(DestinationConn)
        Dim s As StringBuilder = New StringBuilder

        Try
            'Dim t As Table
            Dim t1 As Table
            Dim db As Database
            db = SourceServer.Databases(SourceDB)
            t1 = db.Tables(SourceTable1, SourceSchema)
            TableScripts = t1.Script(CriptingOpt)
            col = TableScripts.GetEnumerator
            While col.MoveNext
                s.Append(" " + col.Current.ToString)
            End While
            s.Replace(TableToCopy, DestinationTableName)
            's = Replace(s, SourceTable1, DestTable1)

            Dim Connection As New SqlClient.SqlConnection(DestConnStr)
            Dim CreateCmd As New SqlClient.SqlCommand(s.ToString, Connection)
            CreateCmd.Connection.Open()
            CreateCmd.ExecuteNonQuery()
            CreateCmd.Connection.Close()


            ' Dts.Variables("User::str_CreateTable").Value = s.ToString
            Dts.TaskResult = ScriptResults.Success
        Catch ex As Exception

            Dts.Events.FireError(0, "Create Table Failure", "Creating the table object Fail " + TableToCopy + " " + ex.Message, "", 0)
            Dts.TaskResult = ScriptResults.Failure
        Finally
            s = Nothing

        End Try

      

    End Sub

End Class