Biml Annotations and ObjectTags

Biml objects have many built-in attributes. For example, all Tables have SchemaName and all Packages have ProtectionLevel. When your Biml solution starts to grow, you will quickly see the need for adding additional metadata that can be used in other Biml files. A common use case in Data Warehouse Staging projects is to store the source schema and source table name on your staging table objects. This allows you to use the source metadata in a higher tier Biml file that generates the SSIS packages to load the tables. To store and use this additional metadata, you can use Biml Annotations or ObjectTags.

Biml Annotations and ObjectTags are both Key/Value pairs. Annotations are String/String pairs intended for storing simple text metadata, while ObjectTags are String/Object pairs that can also store more complex metadata in .NET objects.

Biml Annotations

There are two types of annotations in the Biml language. The first type is SSIS Annotations. An SSIS annotation is a comment inside your SSIS package that is visible on the design surface. This is what most SSIS developers think of when they hear the word “annotation”.

The second type, and the type covered in this blog post, is Biml Annotations. Biml annotations are more than just comments inside a package. They can be used to store additional string metadata on any Biml object, and can also be used in auto-generated documentation.

There are four types of Biml annotations: CodeComment, Documentation, Description, and Tag. You can read more about them in the official AnnotationType documentation.

In this post, we will only look at the Tag annotation type. This is the default Biml annotation type, used for storing string metadata.

You can create one or more annotations directly on objects:

<OleDbConnection Name="Destination" ConnectionString="...">
  <Annotations>
    <Annotation Tag="DestinationDatabase">Staging</Annotation>
    <Annotation Tag="DestinationSchema">aw</Annotation>
  </Annotations>
</OleDbConnection>

To use annotations in a higher tier Biml file, simply call GetTag() on the object:

<#=RootNode.OleDbConnections["Destination"].GetTag("DestinationSchema")#>

Example: Source Table Metadata in Annotations

In this example, we have two tiered Biml files. We define our environment in the first file and our SSIS packages in the second file.

0-Environment.biml:

<#@ template tier="0" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  
  <Connections>
    <OleDbConnection Name="Source" ConnectionString="Data Source=.;Initial Catalog=AdventureWorks;Provider=SQLNCLI11.1;Integrated Security=SSPI;" />
    <OleDbConnection Name="Destination" ConnectionString="Data Source=.;Initial Catalog=Staging;Provider=SQLNCLI11.1;Integrated Security=SSPI;" />
  </Connections>
      
  <Databases>
    <Database Name="Staging" ConnectionName="Destination" />
  </Databases>
      
  <Schemas>
    <Schema Name="aw" DatabaseName="Staging" />
  </Schemas>
  
  <Tables>
    <# var SourceConnection = SchemaManager.CreateConnectionNode("Source", "Data Source=.;Initial Catalog=AdventureWorks;Provider=SQLNCLI11.1;Integrated Security=SSPI;"); #>
    <# var SourceMetadata = SourceConnection.GetDatabaseSchema(ImportOptions.ExcludeViews | ImportOptions.ExcludeIdentity | ImportOptions.ExcludeForeignKey); #>
    <# foreach (var table in SourceMetadata.TableNodes) { #>
      <Table Name="<#=table.Schema#>_<#=table.Name#>" SchemaName="Staging.aw">
        <Columns>
          <#=table.Columns.GetBiml()#>
        </Columns>
        <Annotations>
          <Annotation Tag="SourceSchemaQualifiedName"><#=table.SchemaQualifiedName#></Annotation>
        </Annotations>
      </Table>
    <# } #>
  </Tables>
      
</Biml>

In this file, we define a set of staging tables based on our source metadata. Our staging tables will get the schema aw and the name <#=table.Schema#>_<#=table.Name#>, for example HumanResources_Employee. Since we need the source schema and source table name in the SSIS package to load this table, we add an annotation to the table. The annotation has the tag (key) SourceSchemaQualifiedName and the value <#=table.SchemaQualifiedName#>, for example [HumanResources].[Employee].

1-Packages.biml:

<#@ template tier="1" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Packages>
      <# foreach (var table in RootNode.Tables) { #>
        <Package Name="Load_<#=table.Name#>" ConstraintMode="Linear">
          <Tasks>
            <Dataflow Name="Load <#=table.Name#>">
              <Transformations>
                <OleDbSource Name="Source <#=table.Name#>" ConnectionName="Source">
                  <ExternalTableInput Table="<#=table.GetTag("SourceSchemaQualifiedName")#>" />
                </OleDbSource>
                <OleDbDestination Name="Destination <#=table.Name#>" ConnectionName="Destination">
                  <ExternalTableOutput Table="<#=table.SchemaQualifiedName#>" />
                </OleDbDestination>
              </Transformations>
            </Dataflow>
          </Tasks>
        </Package>
      <# } #>
  </Packages>
</Biml>

In this file, we define a set of SSIS packages based on our staging table definitions. To get the source schema and source table name, we call GetTag() on the table and pass in the tag (key) SourceSchemaQualifiedName: <#=table.GetTag(“SourceSchemaQualifiedName”)#>.

ObjectTags

Like Biml Annotations, ObjectTags can be used to store simple text metadata. They can also be used to store more complex metadata in .NET objects, for example arrays and lists.

The main difference between Biml Annotations and ObjectTags is how they are created. While Biml Annotations can be created directly on Biml objects using the tag, ObjectTags can only be added to existing objects using BimlScript code blocks.

This means that you cannot create a table and add an ObjectTag to it in the same Biml file, like we did in the previous example. You have to create the tables in one file, and add the ObjectTags to the tables in a higher tier Biml file. For use cases like the previous example, Biml Annotations are more suitable than ObjectTags.

So when do we use ObjectTags? A common use case is to add metadata to the RootNode. This allows you to use that metadata in any file in your project, without having to use include files.

You create an ObjectTag by specifying the key directly in brackets:

<# RootNode.ObjectTag["Filter"] = new List{"Product","ProductCategory"}; #>

Use the ObjectTag in the same way, by specifying the key directly in brackets:

<# var Filter = (List)RootNode.ObjectTag["Filter"]; #>

You can also check to see if a specific ObjectTag exists by providing the key or value:

<#=RootNode.ObjectTag.ContainsKey("Key")#>
<#=RootNode.ObjectTag.ContainsValue("Value")#>

Summary

Use Biml Annotations and ObjectTags to store and pass metadata from lower tier to higher tier Biml files. Annotations are String/String pairs intended for storing simple text metadata, while ObjectTags are String/Object pairs that can store more complex metadata in .NET objects.

About the Author

Cathrine Wilhelmsen is a Microsoft Data Platform MVP, international speaker, author, blogger, organizer, and chronic volunteer. She loves data and coding, as well as teaching and sharing knowledge - oh, and sci-fi, gaming, coffee and chocolate 🤓