Roll your own Transfer SQL Server Objects task
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).
