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


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

Comments (28)

  1. SSIS Junkie says:

    Just a quick post. Many people on the SSIS forum have complained about the usefulness of the stock Transfer

  2. santihago says:

    Thanks for your code, very interesting. Only one question: If I want to copy only some tables, what can I do?


  3. gernblanston says:

    I am having the same problem as santihago.  Any word on this?

  4. M.Schwarz says:

    I am using this code above and got the problem, that if there are more then 50 tables to transfer the execution stops after the 50 table with this error message:

    The requested objects failed to transfer.

      at Microsoft.SqlServer.Management.Dts.DtsTransferProvider.ExecuteTransfer()

      at Microsoft.SqlServer.Management.Smo.Transfer.TransferData()

      at ScriptTask_7556849268014ef7b21c42d51ccc6eec.ScriptMain.Main()

    But a transfer with less then 50 tables succeeds.

    if postet this at MSDN Forums:

    but i dont got any solution.

    What can i do?

  5. mmasson says:

    This is a known issue in the SMO layer, and should be fixed in an upcoming cumulative update. The number of tables it can transfer varies depending on the tables being transfered, but typically it’s around 50 or so. Currently the best approach is to split the transfer up across multiple tasks.

  6. M.Schwarz says:

    thank you for the information.

  7. mmasson says:

    To transfer only a couple of tables, take a look at the ObjectList property of the Transfer object.

    Set CopyAllTables to false, and add Table objects to the ObjectList to transfer them.

    Database sourceDB = new Database("source");

    Transfer xfer = new Transfer(sourceDB);

    xfer.CopyAllTables = false;


    xfer.ObjectList.Add( sourceDB.Tables["table1"] );

    xfer.ObjectList.Add( sourceDB.Tables["table2", "schema1"] );

    xfer.ObjectList.Add( sourceDB.Tables["table2", "schema2"] );

    xfer.ObjectList.Add( sourceDB.Tables["table3"] );

  8. SSIS Stuff says:

    I missed some of the comments to my post about creating your own Transfer SQL Objects Task with a script

  9. RobinClark says:

    I was having issues with transferring the column default values with the tables and while trying to resolve it kept getting pointed back to here. So i thought it would be a good idea to post the fix here.

    Use the following to populate the column default values.


  10. jvcoach23 says:

    I’m trying to us parts of this code against a sql 2000 box and get an error that i can’t log into the sql 2000 box.  this is happening on the xfr.transferdata line.

    This is what i have for connecting to the source box

           Dim srvSource As Server

           srvSource = New Server("websql")

           srvSource.ConnectionContext.LoginSecure = False

           srvSource.ConnectionContext.Login = "username"

           srvSource.ConnectionContext.Password = "password"

    was wondering if this is because i’m trying to connect to a sql 2000 box.

  11. mmasson says:

    I haven’t done much work with sql 2000, so I’m not sure how much support for it is in SMO. Your best bet is to post in the SMO forums


  12. kfast says:

    I’ll post this question here and send an email just in case someone else out there needs help or has advice for me.

    I’ve been playing around with programming in SSIS and this post was very helpful to me. I’ve used your example above and I can easily transfer specific tables and data from one server to another…Nice! Almost exactly what I want.

    However, I would like to get this to work using Microsoft.SQLServer.ManagedDTS rather than SMO. Reason being that I have a few other tasks I would like to combine in a sequence and if one fails, I can easily stop the execution of the SSIS package.

    Any chance that you have an example using Microsoft.SqlServer.Dts.Tasks.TransferSqlServerObjectsTask? It seems very similar to the SMO metheod but no matter what I try I can’t get it to work. There is hardly any documentation out there. Here is a copy of the Sub routine I created.

    Private Sub MoveTablesandData(ByRef p As Package)

           Dim tablenames As New System.Collections.Specialized.StringCollection

           Dim mySMOConn As New SqlConnect() ‘ separate class used specifically for connections



           ConnectionName = mySMOConn.ConnnectionName

           ‘p.Executables.Add( _



           Dim tskhost As TaskHost

           Dim tskTransferSQL As TransferSqlServerObjectsTask

           ‘Gotta loop through the executable collection’

           ‘For some reason, the Package.Add method sticks an executable

           ‘anywhere in the collection and not placed at the end like it should.

           For Each executable In p.Executables

               tskhost = CType(executable, TaskHost)

               If tskhost.CreationName.ToLower.IndexOf("transfersqlserver") > -1 Then

                   tskTransferSQL = CType(tskhost.InnerObject, TransferSqlServerObjectsTask)

               End If


           tskTransferSQL.SourceConnection = GetConnectionName()

           tskTransferSQL.SourceDatabase = "FoaeaDBStg2Prod"

           tskTransferSQL.DestinationConnection = GetConnectionName()

           tskTransferSQL.SourceDatabase = "Event"


           tskTransferSQL.TablesList = tablenames

           ‘tskTransferSQL.CopySchema = True

           tskTransferSQL.CopyData = True

       End Sub  

    You mentionned in your original post about setting breakpoints to see the temporary run time packages in SSIS. Maybe you could let me know how to do it?

  13. I need help on this.

    I need to Copy a database from my server with these much property settings but getting error: Like

    ERROR : errorCode=0 description=Incorrect syntax near ‘DUMP’. helpFile= helpContext=0 idofInterfaceWithError={C81DFC5A-3B22-4DA3-BD3B-10BF861A7F9C}

    I am working on sql server 2008

    Please suggest..



  14. mmasson says:


    I’d try to figure out the exact SQL being run to see if you can isolate the problem. One way would be to run SQL Profiler against the source and destination to see the series of statements being executed. See if there is a problem with the SQL syntax – it could be a SMO bug (not handling certain column types properly, etc).


  15. lucifertran says:

    SqlConnectionStringBuilder sourceConnStr = new SqlConnectionStringBuilder();

               sourceConnStr.DataSource = "server";

               sourceConnStr.InitialCatalog = "catalog";

               sourceConnStr.IntegratedSecurity = false;

               sourceConnStr.Password = "pass";

               sourceConnStr.UserID = "userid";

               SqlConnection sourceConn = new SqlConnection(sourceConnStr.ConnectionString);

               SqlConnection destConn = new SqlConnection(sourceConnStr.ConnectionString);

               Server serv = new Server(new ServerConnection(sourceConn));


               Database sourceDB = serv.Databases["sourcedb"];

               Database destDB = new Database(serv, "destinationdb");






               catch (Exception Ex)





               Transfer dbTrans = new Transfer(sourceDB);

               dbTrans.DestinationServer = serv.Name;

               dbTrans.DestinationDatabase = destDB.Name;

               dbTrans.DestinationLoginSecure = false;

               dbTrans.DestinationLogin = "wb";

               dbTrans.DestinationPassword = "wb";

               dbTrans.PreserveLogins = true;

               dbTrans.Options.WithDependencies = true;

               dbTrans.Options.ClusteredIndexes = true;

               dbTrans.Options.ContinueScriptingOnError = true;

               dbTrans.Options.DriAll = true;

               dbTrans.Options.DriAllConstraints = true;

               dbTrans.Options.DriAllKeys = true;

               dbTrans.Options.DriChecks = true;

               dbTrans.Options.DriClustered = true;

               dbTrans.Options.DriDefaults = true;

               dbTrans.Options.DriForeignKeys = true;

               dbTrans.Options.DriIncludeSystemNames = true;

               dbTrans.Options.DriIndexes = true;

               dbTrans.Options.DriNonClustered = true;

               dbTrans.Options.DriUniqueKeys = true;

               dbTrans.Options.DriPrimaryKey = true;

               dbTrans.Options.ExtendedProperties = true;

               dbTrans.Options.FullTextCatalogs = true;

               dbTrans.Options.FullTextIndexes = true;

               dbTrans.Options.IncludeDatabaseContext = true;

               dbTrans.Options.IncludeHeaders = true;

               dbTrans.Options.Indexes = true;

               dbTrans.Options.NoCollation = true;

               dbTrans.Options.Triggers = true;

               dbTrans.CopyAllTables = false;

               dbTrans.CopySchema = false;

               dbTrans.CopyData = true;

               dbTrans.CopyAllObjects = false;

               foreach (var ob in obj)


                   switch (ob.ObjType.ToString().Trim())


                       case "U":



                       case "V":



                       case "P":



                       case "TR":





               StringCollection transferScript = dbTrans.ScriptTransfer();





                       using (SqlCommand switchDatabase = new SqlCommand("USE " + destDB.Name, destConn))




                       foreach (string scriptLine in transferScript)




                               using (SqlCommand scriptCmd = new SqlCommand(scriptLine, destConn))


                                   int res = scriptCmd.ExecuteNonQuery();



                           catch (Exception ex)






                   catch (Exception ex)








    However, i keep getting this error although the connection is set mannually:

    ERROR : errorCode=-1071636471 description=SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E4D.

    An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80040E4D  Description: "Login failed for user ‘WORKBOOKhuynh.tran’.".

    helpFile= helpContext=0 idofInterfaceWithError={5BC870EB-BBA5-4B9D-A6E3-58C6D0051F14}

    Please help me on this, i waste much time but not yet found the cause :(.

  16. lucifertran says:

    The ERROR is at the bottom line.


    Thanks for your kind help!!!

  17. mmasson says:

    The error is that you can’t login.

    Login failed for user ‘WORKBOOKhuynh.tran’

    Are you sure you’re providing the right login credentials? If your database is setup to support windows authentication (the default), then you should set IntegratedSecurity to True and not supply userid and password when you’re setting up the connection string.

  18. lucifertran says:

    Hi, i am connecting to a server using an account. That’s why i use sourceConnStr.IntegratedSecurity = false; and provide and userid and password.

    But DON’T know why it keep using my local account to connect to that server although i have set dbTrans.DestinationLoginSecure = false;

  19. Paul says:

    Question: does SMO transfer class support FILESTREAM?

  20. mmasson says:

    No, I don't think the Transfer class supports FILESTREAM. It might work when moving from one database to another on the same machine/instance, but I'm not sure how it would work against a remote machine.

  21. Paul says:

    Hi Matt,

    I rather expected that would be the case. But asking was easier than setting up an experiment. 😉

    Thanks for the reply.



  22. Manu says:

    Hey Matt,

    This is an Interesting Post. I am also working on something similar, the only difference is i am trying to run same script task with TRANSFER SMO in four different threads(sequence containers) paralleled. It runs fine with smaller tables but when i try to include bigger tables (30 Millions in row size with 100 Columns) it throws a NULLREFERENCE error and i think its because it runs out of memory (cache). It there any way to run it in a controlled manner with all 4 threads running in parallel?

  23. manub22 says:

    How this SMO Transfer object is different from "Transfer SQL Server Objects Task"?

    Which one is better and you recommend?

  24. pl80 says:


    How can I copy all tables that have names starting with 'tmp' using the Transfer SQL Server Object Task?  The number of these tables can change, so I cannot just select them.  Code sample for the script task?  Many thanks.

  25. Rakesh Mishra says:


    Could you please tell me how/where to get the temporary packages that are created on-the-fly.


  26. MahyaDon says:

    Is it possible to get a rowcount if you are copying just the data?

  27. SSIS Junior says:

    Can you Suggest me an approach to how to handle identity columns in this task. becasue when i transfer data from idenitity column where some of the data is missing in between is transfered with idenity_insert ON..which creates diffferent ID's in the new table..

  28. KK says:

    Can you please Share where to look for the details.