Transfering a couple of tables at a time

Transfering a couple of tables at a time

Rate This
  • Comments 2

I missed some of the comments to my post about creating your own Transfer SQL Objects Task with a script task, so I thought I'd post the answer here incase people miss my answer.

You can use the ObjectList property of the Transfer object to specify which database objects you want to transfer. To transfer only certain tables from a database, you'd set CopyAllTables to false, and then add Table objects to the object list.

Note, when accessing the Tables collection of the Database object, you can specify the table by index or by name. You can also use an additional schema name parameter if there are duplicate table names in separate schemas.

 

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

Leave a Comment
  • Please add 4 and 6 and type the answer here:
  • Post
  • Just what I needed, thanks!

    A few other gotchas I've discovered:

    Many of the properties (CopyAllTables, CopyAllObjects, CopyData, etc) seem to default to True, although the msdn claims they default to False.

    I don't think you can use ScriptTransfer() to transfer data, although it's quite handy for debugging schema transfers.

    Also, if a login has access to the model database, TransferData() throws an exception (login already exists) even if you set DropDestinationObjectsFirst and/or Options.IncludeIfNotExists

  • Just what I was looking for. Thanks for the Post...

Page 1 of 1 (2 items)