Roll your own Transfer SQL Server Objects task

Roll your own Transfer SQL Server Objects task

Rate This
  • Comments 28

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

Leave a Comment
  • Please add 7 and 2 and type the answer here:
  • Post
  • Just a quick post. Many people on the SSIS forum have complained about the usefulness of the stock Transfer

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

    Thanks!

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

  • 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: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1648097&SiteID=1

    but i dont got any solution.

    What can i do?

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

  • thank you for the information.

  • 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"] );

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

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

    xfr.Options.DriDefaults=True

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

  • 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

    http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=88&SiteID=1

    Sorry!

  • 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

           'myOLEDBConn.CreateADONETConnection(pkg)

           mySMOConn.CreateSMOConnection(p)

           ConnectionName = mySMOConn.ConnnectionName

           'p.Executables.Add( _

           'GetType(Microsoft.SqlServer.Dts.Tasks.TransferSqlServerObjectsTask.TransferSqlServerObjectsTask).AssemblyQualifiedName)

           p.Executables.Add("STOCK:TransferSqlServerObjectsTask")

           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

           Next

           tskTransferSQL.SourceConnection = GetConnectionName()

           tskTransferSQL.SourceDatabase = "FoaeaDBStg2Prod"

           tskTransferSQL.DestinationConnection = GetConnectionName()

           tskTransferSQL.SourceDatabase = "Event"

           tablenames.Add("[dbo].[Act_Summons]")

           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?

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

    Thanks

    Amit

  • Amit,

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

    ~Matt

  • 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));

               serv.ConnectionContext.Connect();

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

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

               try

               {

                   serv.KillAllProcesses(destDB.Name);

                   serv.Databases["destinationdb"].Drop();

               }

               catch (Exception Ex)

               {

                   Console.WriteLine(Ex.ToString());

               }

               destDB.Create();

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

                           dbTrans.ObjectList.Add(sourceDB.Tables[ob.Name]);

                           break;

                       case "V":

                           dbTrans.ObjectList.Add(sourceDB.Views[ob.Name]);

                           break;

                       case "P":

                           dbTrans.ObjectList.Add(sourceDB.StoredProcedures[ob.Name]);

                           break;

                       case "TR":

                           dbTrans.ObjectList.Add(sourceDB.Triggers[ob.Name]);

                           break;

                   }

               }

               StringCollection transferScript = dbTrans.ScriptTransfer();

               {

                   try

                   {

                       destConn.Open();

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

                       {

                           switchDatabase.ExecuteNonQuery();

                       }

                       foreach (string scriptLine in transferScript)

                       {

                           try

                           {

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

                               {

                                   int res = scriptCmd.ExecuteNonQuery();

                               }

                           }

                           catch (Exception ex)

                           {

                               Console.WriteLine(ex.ToString());

                           }

                       }

                   }

                   catch (Exception ex)

                   {

                       Console.WriteLine(ex.ToString());

                   }

               }

               dbTrans.TransferData();

               destConn.Close();

               serv.ConnectionContext.Cancel();

    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 'WORKBOOK\huynh.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 :(.

Page 1 of 2 (28 items) 12