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.

Published: Last Updated: Categories: BimlTags:

About the Author

Cathrine Wilhelmsen is a Microsoft Data Platform MVP, BimlHero Certified Expert, Microsoft Certified Solutions Expert, international speaker, author, blogger, and chronic volunteer who loves teaching and sharing knowledge. She works as a Senior Business Intelligence Consultant at Inmeta, focusing on Azure Data and the Microsoft Data Platform. 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. Be kind to each other. Thanks!

Excellent post! Very timely, this is a topic i spent all last week struggling with!

As usual, very valuable and interesting post, many thanks.

Is GetDatabaseSchema available using an ODBC source connection?

No, it’s not :(

It’d be nice if it was, because ODBC also has an analogous GetSchema method like OLEDB does. They work more or less the same.

Well, in .Net world:
System.Data.OleDb
has the OleDbConnection.GetSchema() method. Its output goes into a DataTable object. You could write BimlScript to interrogate your OleDB connection, possibly. I believe this is what BIML is hooking into.

Then, you could filter the DataTable, perhaps with regular expressions.

IIRC, the ODBC version of GetSchema() has some quirks (bugs), when trying to get Indexes – you need to get all of them, as filtering in the call doesn’t work… I don’t remember if this behavior is also “included” in the OleDb version.

Hi Cathrine,

Very useful post. How would go about if I want to store the values for the includedTables list in a csv file in the project? And I want to access the csv file using a relative path?

Great post! Is it possible to do a pattern match on includedTables for the GetDatabaseSchema call? I can’t find any decent documentation on that subject.

Pingback: Create a Staging Load with Biml - Tim Mitchell

Hi Cathrine, I need some help… I had copy and pasted, but it failed when it should create a composite Primary Key. In that particular case, there was two column missing; for instance, the retrieved code was

CREATE TABLE [Sales].[CountryRegionCurrency]
(
— Columns Definition
[ModifiedDate] datetime NOT NULL

— Constraints
,CONSTRAINT [PK_CountryRegionCurrency_CountryRegionCode_CurrencyCode] PRIMARY KEY NONCLUSTERED
(
[CountryRegionCode] Asc,[CurrencyCode] Asc) WITH(PAD_INDEX = OFF,IGNORE_DUP_KEY = OFF) ON “default”

)
ON “default”
WITH (DATA_COMPRESSION = NONE)
GO

My source database is AdventureWorks2016CTP3

Could you guide me to the solution?

Thanks!

PS: Sorry for my English

Hi Jose, there are some known issues with GetDropAndCreateDdl, including missing columns. Since this is a known bug, I can unfortunately not do much to help. If you can’t wait for a fix to be released, please reach out to Varigence Support.

Hi Cathrine, I am trying to buikld a biml script that will draw across tables views indexes and Stored procedures. I have used the example you posted on this page but it does not bring across stored procs, is there a way to get stored procedures from the same schema with GetDatabasechema()

Kind Regards
David

Hi! This is Cathrine (again). Just a reminder. 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. Be kind to each other. Thanks!

Leave a Reply to Frank Cancel reply