SSIS: Transfer SQL Server Object using SMO

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 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

Copyright © 2012 Vinoth N Manoharan.The information provided in this post is provided "as is" with no implied warranties or guarantees.