Transfer SQL Server Objects Between Databases

Builds a 'Transfer.DataBase.Objects' package whose single 'TransferSqlServerObjects' task copies three named tables (Department, Employee, EmployeeDepartmentHistory from the HumanResources schema) from 'AdventureWorks2012' to 'AdventureWorksDW2012'. Both endpoints share the same 'SqlServerManagementConnection' against the local MSSQL2012 instance with Windows authentication, and the task is configured with 'DropObjectsFirst="true"' and 'CopyData="true"' so the destination is rebuilt and refilled on every run.

Schema-qualify each entry in the 'Tables' element with bracketed notation ('[schema].[TableName]'). The destination schema must already exist; the task creates the objects but not their parent schemas. Set 'CopyAllTables="true"' to copy everything in the source database without listing tables individually.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <SqlServerManagementConnection
      Name="SSMCLocalHostMSSQL2012"
      SqlServerName=".\MSSQL2012"
      UseWindowsAuthentication="true"></SqlServerManagementConnection>
  </Connections>
  <Packages>
    <Package Name ="Transfer.DataBase.Objects" ConstraintMode="Linear">
      <Tasks>
        <TransferSqlServerObjects
          Name ="TransferSqlServerObjects"
          SourceConnectionName="SSMCLocalHostMSSQL2012"
          SourceDatabase="AdventureWorks2012"
          DestinationConnectionName="SSMCLocalHostMSSQL2012"
          DestinationDatabase="AdventureWorksDW2012"
          DropObjectsFirst="true"
          CopyData="true"
          CopyAllTables="false">
          <Annotations>
            <Annotation>
              Notation for tables:
              [schema].[TableName]
              (including bracktes}
              Target schema must exist!
            </Annotation>
          </Annotations>
          <Tables>
            <Table>[HumanResources].[Department]</Table>
            <Table>[HumanResources].[Employee]</Table>
            <Table>[HumanResources].[EmployeeDepartmentHistory]</Table>
          </Tables>
        </TransferSqlServerObjects>

      </Tasks>
    </Package>
  </Packages>
</Biml>

---

Submitted by John Minkjan.