Create and Load a Staging Environment from Scratch in an Hour with Biml

The video that accompanies these code samples is Create and Load a Staging Environment from Scratch in an Hour with Biml on YouTube, originally presented at the PASS BI Virtual Chapter on 2013/12/12. Below are the 4 files, along with their names for use in either BimlExpress or BimlStudio:

1-2-Environment.biml

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <OleDbConnection Name="Source" ConnectionString="Provider=SQLNCLI11;Server=localhost;Initial Catalog=AdventureWorks2012;Integrated Security=SSPI;" />
    <OleDbConnection Name="Target" ConnectionString="Provider=SQLNCLI11;Server=localhost;Initial Catalog=DataPatternsStage;Integrated Security=SSPI;" />
  </Connections>
  <Databases>
    <Database Name="DataPatternsStaging" ConnectionName="Target" />
  </Databases>
  <Schemas>
    <Schema Name="stg" DatabaseName="DataPatternsStaging" />
  </Schemas>
</Biml>

1-2-CreateTableMetadata.biml

<#@ import namespace="Varigence.Hadron.CoreLowerer.SchemaManagement"  #>

<#
var sourceConnection = RootNode.DbConnections["Source"];
var importResult = sourceConnection.ImportDB("", "", ImportOptions.ExcludeForeignKey | ImportOptions.ExcludeColumnDefault | ImportOptions.ExcludeViews);
#>

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Tables>
    <# foreach (var table in importResult.TableNodes) { #>
    <Table Name="Staging_<#=table.Schema.Name#>_<#=table.Name#>" SchemaName="DataPatternsStaging.stg">
      <Columns>
        <# foreach (var column in table.Columns) { #>
        <# if (column.DataType == System.Data.DbType.AnsiString) { #>
        <Column Name="<#=column.Name#>" DataType="String" Length="<#=column.Length#>">
          <Annotations>
            <Annotation AnnotationType="Tag" Tag="ConvertToUnicode">yes</Annotation>
          </Annotations>
        </Column>
        <# } else { #>
        <#=column.GetBiml()#>
        <# } #>
        <# } #>
      </Columns>
      <Annotations>
        <Annotation AnnotationType="Tag" Tag="SourceSchemaQualifiedName"><#=table.SchemaQualifiedName#></Annotation>
      </Annotations>
    </Table>
    <# } #>
  </Tables>
</Biml>

1-x-DeployTargetTables.biml

<#@ template tier="2" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Packages>
    <Package Name="MasterTableDeploy" ConstraintMode="Parallel">
      <Tasks>
        <# foreach (var table in RootNode.Tables) { #>
        <ExecuteSQL Name="Create <#=table.Name#>" ConnectionName="Target">
          <DirectInput><#=table.GetDropAndCreateDdl()#></DirectInput>
        </ExecuteSQL>
        <# } #>
      </Tasks>
    </Package>
  </Packages>
</Biml>

x-2-CreateLoadPackages.biml

<#@ template tier="2" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Packages>
    <Package Name="Copy Data" ConstraintMode="Parallel">
      <Tasks>
        <# foreach (var table in RootNode.Tables) { #>
        <Dataflow Name="Copy DataPatterns_<#=table.Schema.Name#>_<#=table.Name#>">
          <Transformations>
            <OleDbSource Name="Retrieve Rows" ConnectionName="Source">
              <DirectInput>SELECT <#=table.GetColumnList()#> FROM <#=table.GetTag("SourceSchemaQualifiedName")#></DirectInput>
            </OleDbSource>
            <# foreach (var column in table.Columns.Where(c => c.GetTag("ConvertToUnicode") == "yes")) { #>
            <DataConversion Name="Convert <#=column.Name#>">
              <Columns>
                <Column SourceColumn="<#=column.Name#>" TargetColumn="<#=column.Name#>" DataType="String" Length="<#=column.Length#>" />
              </Columns>
            </DataConversion>
            <#} #>
            <OleDbDestination Name="Load Rows" ConnectionName="Target">
              <TableOutput TableName="<#=table.ScopedName#>" />
            </OleDbDestination>
          </Transformations>
        </Dataflow>
        <# } #>
      </Tasks>
    </Package>
  </Packages>
</Biml>