Biml Extension Methods: GetDatabaseSchema

Biml (Business Intelligence Markup Language)There 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>:

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

Conclusion
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, speaker, blogger and chronic volunteer. She currently works as a Community Evangelist for PASS and coordinates all SQLSaturdays around the world, but has previously worked as a SQL Server Data Warehouse architect and Business Intelligence developer. She loves sci-fi, chocolate, cat gifs and smilies :)

11 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=”http://schemas.varigence.com/biml.xsd”>
<#@ template tier=”0″ #>
<Connections>
<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”/>
</Connections>
<Databases>
<Database Name=”STAGE” ConnectionName=”STAGE” />
</Databases>
<Schemas>
<Schema Name=”BEHEER” DatabaseName=”STAGE” />
</Schemas>
</Biml>

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=”http://schemas.varigence.com/biml.xsd”>
<Packages>
<Package Name=”DropCreateStagingTables” ConstraintMode=”Linear”>
<Tasks>
<# foreach (var table in SRCDB.TableNodes) { #>
<ExecuteSQL Name=”Drop and Create <#=table.Name#>” ConnectionName=”STAGE”>
<DirectInput><#=table.GetDropAndCreateDdl()#></DirectInput>
</ExecuteSQL>
<# } #>
</Tasks>
</Package>
</Packages>

</Biml>

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

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.

Share Your Thoughts?