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


Microsoft.SqlServer.ConnectionInfo
Microsoft.SqlServer.Smo
Microsoft.SqlServer.SmoEnum


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)



dbCopy.Create()



'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


xfr.TransferData()



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: