Skip to content

Import Metadata in Biml using GetDatabaseSchema

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<string>.

MethodDescription
GetDatabaseSchema()Import all schemas, tables and views.
GetDatabaseSchema(ImportOptions)Import all schemas, tables and views. Exclude objects by ImportOptions.
GetDatabaseSchema(IncludedSchemas,IncludedTables,ImportOptions)Import specific schemas, tables and/or views. Exclude objects by ImportOptions.
ParameterDescriptionData TypeExamples
ImportOptionsObjects to exclude from importImportOptionsImportOptions.None
ImportOptions.ExcludeCheckConstraint
ImportOptions.ExcludeColumnDefault
ImportOptions.ExcludeForeignKey
ImportOptions.ExcludeIdentity
ImportOptions.ExcludeIndex
ImportOptions.ExcludePrimaryKey
ImportOptions.ExcludeUniqueKey
ImportOptions.ExcludeViews
IncludedSchemasSchema(s) to importIEnumerable<string>null
new List<string>()
new List<string>{"Production"}
IncludedTablesTable(s) to importIEnumerable<string>null
new List<string>()
new List<string>{"Product","ProductCategory","ProductSubcategory"}

Example usage

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.

Who is Cathrine Wilhelmsen?

Cathrine is a Microsoft Data Platform MVP, BimlHero, Microsoft Certified Solutions Expert, author, speaker, blogger and chronic volunteer who loves teaching and sharing knowledge. She works as a consultant, architect and developer, focusing on Data Warehouse and Business Intelligence projects. She loves sci-fi, chocolate, coffee, craft beers, ciders, cat gifs and smilies :)

Comments

Hi! This is Cathrine. Thank you so much for visiting my blog. I'd love to hear your thoughts, but please keep in mind that I'm not technical support for any products mentioned in this post :) Off-topic questions, comments and discussions may be moderated. Thanks!

Click to Show / Hide Comments