Copy All Data from Live Source Database

In previous snippets such as 'Import Database Assets into Biml' and 'Extract All Tables,' we saw examples of how to do a simple copy of all tables in a project and how to directly access a live database from Biml. In this snippet, we will bring the two examples together to directly copy all tables from a source database into a target, without any required fore-knowledge of the source schema. This sample could be supplemented with a second script that would create the target schema prior to attempting the copy. For an example of that, check out 'Use GetTableSql() Utility Extension to Create T-SQL CREATE TABLE Scripts.'

<#@ import namespace="Varigence.Hadron.CoreLowerer.SchemaManagement" #>
<#
// This is inelegant.  We would normally use an approach similar to /bimlscript/snippets/import-database-assets-into-biml where we reference an existing connection.
// In this case, we are instantiating a new connection in code, so that the sample is entirely self-contained in one file.
var sourceConnectionString = "Provider=SQLNCLI10;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=DataPatterns"; 
var connection = new AstOleDbConnectionNode(null) { ConnectionString = sourceConnectionString }; 
var importResult = connection.ImportDB("","",ImportOptions.ExcludeForeignKey | ImportOptions.ExcludeColumnDefault | ImportOptions.ExcludeViews);#>

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <OleDbConnection Name="Source" ConnectionString="<#=sourceConnectionString #>" />
    <OleDbConnection Name="Target" ConnectionString="Provider=SQLNCLI10;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=DataPatterns" />
  </Connections>
  <Packages>
    <Package Name="Copy All" ConstraintMode="Parallel" AutoCreateConfigurationsType="None">
      <Tasks>
        <!-- Creates a new dataflow for each table in the target DB -->
        <# foreach (var table in importResult.TableNodes) { #>
        <Dataflow Name="Copy <#=table.Name#>">
          <Transformations>
            <OleDbSource Name="Retrieve <#=table.Name#>" ConnectionName="Source">
              <DirectInput>SELECT * FROM <#=table.SchemaQualifiedName#></DirectInput>
            </OleDbSource>
            <OleDbDestination Name="Load <#=table.Name#>" ConnectionName="Target">
              <ExternalTableOutput Table="<#=table.SchemaQualifiedName#>"/>
            </OleDbDestination>
          </Transformations>
        </Dataflow>
        <# } #>
      </Tasks>
    </Package>
  </Packages>
</Biml>