Roll your own Transfer SQL Server Objects task

Published Mar 25 2019 01:56 PM 582 Views
Not applicable
First published on MSDN on Apr 18, 2007

Awhile back we went through a lot of customer feedback logged through the Connect site concerning the Transfer SQL Server Objects task. Some of these turned out to be bugs, but a lot of the reported issues stemmed from usability problems with the task itself. There's actually not that much to the task on the SSIS end – it uses the SQL Server Management Objects (SMO) API to do the bulk of the work. Anyone familiar with the SMO Transfer object will notice the similarities in the task's UI. We end up exposing the majority of the Transfer object's members as properties, which leads to most of the reported problems with the task; it's complicated, and options sometimes conflict with each other.

It's always a challenge to get the right balance of usability and functionality, and we're hoping to address the problems with this task in a future release (suggestions are always welcome). In the mean time, you can always "roll your own" version of task using the SMO API directly through a script task.

First you'll need to add references to the three SMO assemblies (found under Microsoft SQL Server\90\SDK\Assemblies):


Import the SMO namespaces in your code:

Imports Microsoft.SqlServer.Management.Smo

Imports Microsoft.SqlServer.Management.Common

The following code was adapted from one of the samples on MSDN. It creates a new database on your local server, and sets up a Transfer object to copy across all tables, dependencies, and data from the AdventureWorks database.

Public Sub Main()

Dim dbSourceName As String = "AdventureWorks"

Dim dbDestName As String = dbSourceName + "Copy"

'Connect to the local, default instance of SQL Server.

Dim srv As Server

srv = New Server

'Reference the source database

Dim db As Database

db = srv.Databases(dbSourceName)

'Create a new database that is to be destination database.

Dim dbCopy As Database

dbCopy = New Database(srv, dbDestName)


'Define a Transfer object and set the required options.

Dim xfr As Transfer

xfr = New Transfer(db)

xfr.CopyAllTables = True

xfr.Options.WithDependencies = True

xfr.Options.ContinueScriptingOnError = True

xfr.DestinationDatabase = dbCopy.Name

xfr.DestinationServer = srv.Name

xfr.DestinationLoginSecure = True

xfr.CopySchema = True

'Include data

xfr.CopyData = True

'Execute the transfer


Dts.TaskResult = Dts.Results.Success

End Sub

Another useful method of the Transfer object is ScriptTransfer() – it returns the T-SQL script of what you've configured your Transfer object to do. You can dump this to debug any issues you run into, and to figure out exactly what SMO is doing.

Interestingly, SMO actually uses SSIS internally to do its data transfer. It creates a couple of temporary packages on-the-fly, and runs behind the scenes. (It's possible to capture the scripts and packages if you set break points in the right place – send me an email or leave comments if you're interested in how to do this).

1 Comment
Version history
Last update:
‎Mar 25 2019 01:56 PM
Updated by: