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
