we often face a situation to move SQL server object beween servers or databases, when you have constant table list we can always use "Transfer SQL Server Objects Task" with the SSIS, One of my customer had a requirement of scheduling migration process frequently and would be supplying the table list dynamic every time using a meta data table. When i started to develop the SSIS to accomplish this task i realised the "Transfer SQL Server Objects Task" does not accept variable of Object data type with the table list as expression. So i decided to use SMO to copy SQL server objects using xfr.TransferData() function which has more flexible option.
You can change various options to the below script, I have created the script to create a table list and migrate to destination with data.
First you'll need to add references to the three SMO assemblies (found under Microsoft SQL Server\90\SDK\Assemblies):
Microsoft.SqlServer.ConnectionInfo
Microsoft.SqlServer.Smo
Microsoft.SqlServer.SmoEnum
Microsoft.SqlServer.Smo
Microsoft.SqlServer.SmoEnum
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Common
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
' 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.
Public Sub Main()
Dim dbSourceName As String
Dim dbDestName As String
Dim tbl_name1 As String
Dim SQLServername As String
'Set Source and destination database
dbSourceName = Dts.Variables("sourcedb_name").Value.ToString()
dbDestName = Dts.Variables("destdb_name").Value.ToString()
'Connect to the local, default instance of SQL Server.
SQLServername = Dts.Variables("SQL_Name").Value.ToString()
Dim srv As Server
srv = New Server(SQLServername)
'Reference the source database
Dim db As Database
db = srv.Databases(dbSourceName)
'Reference the destination database.
Dim dbCopy As Database
dbCopy = New Database(srv, dbDestName)
'Table name
tbl_name1 = Dts.Variables("Tbls_name").Value.ToString()
'Define a Transfer object and set the required options.
'MsgBox(tbl_name1)
Dim xfr As Transfer
xfr = New Transfer(db)
xfr.CopyAllDatabaseTriggers = False
xfr.CopyAllDefaults = False
xfr.CopyAllLogins = False
xfr.CopyAllObjects = False
xfr.CopyAllPartitionFunctions = False
xfr.CopyAllPartitionSchemes = False
xfr.CopyAllRoles = False
xfr.CopyAllRules = False
xfr.CopyAllSchemas = False
xfr.CopyAllSqlAssemblies = False
xfr.CopyAllStoredProcedures = False
xfr.CopyAllSynonyms = False
xfr.CopyAllTables = False
xfr.CopyAllUserDefinedAggregates = False
xfr.CopyAllUserDefinedDataTypes = False
xfr.CopyAllUserDefinedFunctions = False
xfr.CopyAllUserDefinedTypes = False
xfr.CopyAllUsers = False
xfr.CopyAllViews = False
xfr.CopyAllXmlSchemaCollections = False
xfr.CreateTargetDatabase = False
xfr.DropDestinationObjectsFirst = False
xfr.PrefetchObjects = False
xfr.SourceTranslateChar = False
'Add Table to the List
xfr.ObjectList.Add(db.Tables(tbl_name1))
xfr.Options.WithDependencies = True
xfr.Options.Indexes = True
xfr.Options.DriAll = True
xfr.CopySchema = True
xfr.DestinationDatabase = dbCopy.Name
xfr.DestinationServer = srv.Name
'Include data
xfr.CopyData = True
'Execute the transfer
xfr.TransferData()
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
