Transformer - Add default columns to table example

<#@ target type="Table"  mergemode="LocalMerge"#>
<#@ import namespace="System.Collections.Generic" #>
<#
    // -------------------------------------------------------------- 
    // List of metadata columns which are fixed for my staging tables
    // using \ to escapte quotes inside the string 
   // Example
   // HASHVALUE_KEY -> <Column Name="HASHVALUE_KEY" DataType="Binary" Precision="32"  IsNullable="false" />
    // -------------------------------------------------------------- 
    var metadataColumns = new Dictionary<string,string>();
    metadataColumns.Add("HASHVALUE_KEY","<Column Name=\"HASHVALUE_KEY\" DataType=\"Binary\" Precision=\"32\"  IsNullable=\"false\" />");
    metadataColumns.Add("HASHVALUE_KEY_VERSION","<Column Name=\"HASHVALUE_KEY_VERSION\" DataType=\"Int32\"  IsNullable=\"false\" />");
    metadataColumns.Add("HASHVALUE","<Column Name=\"HASHVALUE\" DataType=\"Binary\" Precision=\"64\"  IsNullable=\"false\" />");
    metadataColumns.Add("ACTIVE","<Column Name=\"ACTIVE\" DataType=\"Int16\"  IsNullable=\"false\" />");
    metadataColumns.Add("RSRC","<Column Name=\"RSRC\" DataType=\"String\" Length=\"255\" Default=\"'To be filled by ETL during load'\" IsNullable=\"false\" />");
    metadataColumns.Add("LDTS","<Column Name=\"LDTS\" DataType=\"DateTime2\" Default=\"getdate()\" IsNullable=\"false\" />");

      // -----------------------------------------------------------------------------
      // Get list of columns from table and add technical columns if they do not exist
      // -----------------------------------------------------------------------------
      var tableCols = TargetNode.GetColumnList();
      string listOfColumnsToAdd = "<!--Added technical metadata columns (see addMetadataColumns.bimlt) -->";
      foreach (var metadataColumn in metadataColumns){
        if (!tableCols.Contains(metadataColumn.Key))
          listOfColumnsToAdd +=metadataColumn.Value;
      }

    #>
<Table>
  <Columns>
    <#=listOfColumnsToAdd #>
  </Columns>
</Table>

This snippet was inspired by a related snippet to check the existence of a column to be added in a transformer, enhanced a little bit because I did not want to clutter the XML with a lot of <# if (...) #> statements. So I put the columns I needed in a dictionary structure in form of Column Name -> BIML column content

Then in a second step I iterate over the columns I want to add, check if they exist and add them to a string if they do. What I add is the XML that I need for that column.

In BimlStudio this shows up like this in the Live Preview when the columns do not exist yet (see attached file ExpandedBimlScript_screenshot.png).

When you run the transformer on the table (in logical view mark the table right click and choose Execute Transformers) the columns will be added (see attached file Added_Columns_in_table_biml.png).

If you run the Transformer a second time it will notice that the columns already exist and not add them a second time. See Scott's article on how to execute the transformer automatically during every build (Section Transformer Frameworks)

<Biml xmlns="http://schemas.varigence.com/biml.xsd">

</Biml>