Biml Extension Methods: GetDatabaseSchema

Biml (Business Intelligence Markup Language) - GetDatabaseSchemaThere are three Biml extension methods for importing metadata from a database. GetDatabaseSchema, ImportTableNodes and ImportDB. They all return an ImportResults object that contains SchemaNodes (schema metadata) and TableNodes (table and view metadata).

This post is an introduction to GetDatabaseSchema (which is currently not documented in the Biml Extensions Quick Reference). How do you use this method and how is it different from ImportTableNodes and ImportDB?

Important update in June 2015: ImportDB is slow, buggy and is therefore being deprecated. Use GetDatabaseSchema instead.

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>:

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
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
In this example I have used two tiered Biml files to separate and reuse code. It shows how to import metadata from the AdventureWorks2014 database to create a package that drops and creates all tables in a Staging database.

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.

This post is an introduction to the Biml extension 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 :)

18 thoughts on “Biml Extension Methods: GetDatabaseSchema”

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

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

Hello Cathrine,

I am stuck with ‘translating’ your example to our specific situation:

First Biml file looks like this and validates without errors:

<Biml xmlns=””>
<#@ template tier=”0″ #>
<OleDbConnection Name=”SRC” ConnectionString=”Data Source=xxx;User ID=xxx;password=xxx;Initial Catalog=xxx;Provider=SQLNCLI11.1;Auto Translate=False;” CreateInProject=”true”/>
<OleDbConnection Name=”STAGE” ConnectionString=”Data Source=xxx;User ID=xxx;password=xxx;Initial Catalog=STAGE;Provider=SQLNCLI11.1;Auto Translate=False;” CreateInProject=”true”/>
<Database Name=”STAGE” ConnectionName=”STAGE” />
<Schema Name=”BEHEER” DatabaseName=”STAGE” />

The second Biml file looks like this and does not validate:
<#@ template tier=”1″ #>
<#@ import namespace=”Varigence.Biml.CoreLowerer.SchemaManagement” #>

<# var includedSchemas = new List<string>{“dbo”}; #>
<# var includedTables = new List<string>{“ORD”}; #>

<# var conSRC = RootNode.OleDbConnections[“SRC”]; #>
<# var SRCDB = conSRC.GetDatabaseSchema(); #>

<Biml xmlns=””>
<Package Name=”DropCreateStagingTables” ConstraintMode=”Linear”>
<# foreach (var table in SRCDB.TableNodes) { #>
<ExecuteSQL Name=”Drop and Create <#=table.Name#>” ConnectionName=”STAGE”>
<# } #>


Error message:

Tables.biml(65863,186) : Error 0 : Name cannot begin with the ‘=’ character, hexadecimal value 0x3D. Line 65863, position 186. Exception type: XmlException
Parse. There were errors during compilation. See compiler output for more information.

Any idea? I already tried putting this in front and also on the position as mentioned above, but that does not change anything.

Thanks in advance

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

I keep getting this error:

BimlScript code produced an exception: Supplied connections must be of type AstDbConnectionNode for this method. The specific connection is an OleDbConnection which should be returning an instance of AstOleDbConnectionNode.

Well, it works if I split the connections and the tables into separate files like the example. From the text, I thought that was optional but apparently it’s necessary.

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 am stuck with a situation where I have to use ExternalDataAccess.GetDataTable(,) and I cannot get connection string in a variable to use in this GetDataTable method. can you help with this first parameter? how do I get connection string in a variable so I can pass in this parameter?
I saw you’re code and got hopeful that it can be found:


I have added both the biml code into the individual biml scripts(modified connection server to our server).
When i ‘check BIML for errors’ the 1st one works fine. but the Tier1 does not work.

The supplied connection is null.
at Varigence.Biml.Extensions.ExternalDataAccess.GetDatabaseSchema
line 10. Exception type: InvalidOperationException

Could you please help?


I’m having an issue where the connection seems to end up in the master system database instead of the one specified in the connection string. It has been an intermittent issue in the past but now I can’t seem to get it to go away. Previously a VS restart worked but not this time.

Any ideas?

i have got the following exception
The supplied connection is null.

The supplied connection is null.

I was able to make this work but the generated DDL was not able to generate the definition for a computed column. Does GetDropAndCreateDdl support computed columns?

Share Your Thoughts?

This site uses Akismet to reduce spam. Learn how your comment data is processed.