Import Metadata in Biml using GetDatabaseSchema

One of the main tasks in Biml projects is to import source metadata. The recommended method is GetDatabaseSchema. This method returns an ImportResults object that contains SchemaNodes (schema metadata) and TableNodes (table and view metadata).

Methods and Parameters

GetDatabaseSchema does not require any parameters, but can take three parameters: ImportOptions, IncludedSchemas and IncludedTables. If no parameters are provided, it will import all schemas, tables and views. Certain objects like views, keys or indexes can be excluded by passing the ImportOption parameter.

The IncludedSchemas and IncludedTables parameters are collections of strings that allow you to specify which schemas and tables/views to import. There are many collections and many ways to create these collections. To keep this blog post simple, I will only use List.

Example usage of GetDatabaseSchema

The example below shows how to import metadata from the AdventureWorks database to create a package that drops and creates all tables in a Staging database.

<# 
  var sourceConnection = SchemaManager.CreateConnectionNode("Source", @"Data Source=.;Initial Catalog=AdventureWorks;Provider=SQLNCLI11;Integrated Security=SSPI;");
  var sourceMetadata = sourceConnection.GetDatabaseSchema();
#>

<#* 
  /* More examples of how to use GetDatabaseSchema to exclude objects */
  var sourceMetadata = sourceConnection.GetDatabaseSchema();
  var sourceMetadata = sourceConnection.GetDatabaseSchema(ImportOptions.ExcludeViews);
  var sourceMetadata = sourceConnection.GetDatabaseSchema(ImportOptions.ExcludeCheckConstraint | ImportOptions.ExcludeColumnDefault | ImportOptions.ExcludeForeignKey | ImportOptions.ExcludeIdentity | ImportOptions.ExcludeIndex | ImportOptions.ExcludePrimaryKey | ImportOptions.ExcludeUniqueKey | ImportOptions.ExcludeViews);
  
  /* More examples of how to use GetDatabaseSchema to filter schemas and tables */
  var includedSchemas = new List<string>{"Production"};
  var includedTables = new List<string>{"Product","ProductCategory","ProductSubcategory"};
  var sourceMetadata = sourceConnection.GetDatabaseSchema(includedSchemas, includedTables, ImportOptions.None);
  var sourceMetadata = sourceConnection.GetDatabaseSchema(null, includedTables, ImportOptions.None);
  var sourceMetadata = sourceConnection.GetDatabaseSchema(includedSchemas, null, ImportOptions.None);

*#>

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <OleDbConnection Name="Staging" ConnectionString="Data Source=.;Initial Catalog=Staging;Provider=SQLNCLI11;Integrated Security=SSPI;" />
  </Connections>
  <Packages>
    <Package Name="DropCreateStagingTables" ConstraintMode="Linear">
      <Tasks>
        <# foreach (var table in sourceMetadata.TableNodes) { #>
          <ExecuteSQL Name="Drop and Create <#=table.Schema#> <#=table.Name#>" ConnectionName="Staging">
            <DirectInput><#=table.GetDropAndCreateDdl() #></DirectInput>
          </ExecuteSQL>
        <# } #>
      </Tasks>
    </Package>
  </Packages>
</Biml>

How is GetDatabaseSchema different from ImportTableNodes and ImportDB?

The main limitation of ImportTableNodes and ImportDB is that you can not specify a collection of schemas, tables or views to import. You can only specify patterns by using wildcards. For example, you can import all tables that begin with a P by using a wildcard, but you can not choose to import just the three tables Customer, Employee and Person. GetDatabaseSchema allows you to specify collections of schemas, tables and views to import and it also performs better than ImportTableNodes and ImportDB.

Summary

This post is an introduction to the Biml method GetDatabaseSchema. It shows how to use the method by creating and providing parameters. GetDatabaseSchema allows you to specify collections of schemas, tables and views to import, or to import everything in a database. It is also possible to exclude objects like views, keys and indexes.

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 🤓