Skip to content

Import Metadata in Biml using ImportTableNodes and ImportDB

↓ Click to read full article

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

In this post, you will learn how to use ImportTableNodes and ImportDB. (Both methods are currently documented in the Biml Extensions Quick Reference). What is the difference between the two methods and how do you use them?

Differences between ImportTableNodes and ImportDB

The main difference between the two methods is that ImportTableNodes is restricted to importing one schema and the tables/views belonging to that schema, while ImportDB can import all schemas, tables and views. If you need to import everything in a database or import multiple schemas you can use ImportDB. If you only need to import a single table or import tables belonging to a single schema, you can use both methods.

You choose which objects to import by passing parameters to the methods. The parameters work as filters to get one or more objects.

Methods and Parameters

ImportTableNodes requires two string parameters: Schema and TableFilter. Schema needs to be the name of a specific schema, it can’t be empty and it is not possible to use wildcards. TableFilter can be empty to import all tables/views in the schema, it can use wildcards to import certain tables/views in the schema, or it can be the name of a specific table/view in the schema. Certain objects like views, keys or indexes can be excluded by passing the third (optional) parameter ImportOptions.

ImportDB does not require any parameters, but can take two string parameters: SchemaFilter and TableFilter. If no parameters are provided it will import all schemas, tables and views. Empty SchemaFilter and TableFilter parameters is equal to not providing any parameters at all, but can be used with ImportOptions to import all schemas and tables while excluding certain objects like views, keys or indexes. Both SchemaFilter and TableFilter can use wildcards.

MethodDescription
ImportTableNodes(Schema,TableFilter)Import the schema, and tables/views filtered by both Schema and TableFilter.
ImportTableNodes(Schema,TableFilter,ImportOptions)Import the schema, and tables/views filtered by both Schema and TableFilter. Exclude objects by ImportOptions.
ImportDB()Import all schemas, tables and views.
ImportDB(SchemaFilter,TableFilter)Import schemas filtered by SchemaFilter, and tables/views filtered by both SchemaFilter and TableFilter.
ImportDB(SchemaFilter,TableFilter,ImportOptions)Import schemas filtered by SchemaFilter, and tables/views filtered by both SchemaFilter and TableFilter. Exclude objects by ImportOptions.
ParameterDescriptionData TypeWildcardsExamples
SchemaSchema to importstringNo"Person"
"dbo"
SchemaFilterSchema(s) to importstringYes"Person"
"P%"

"dbo"
"d[abc]o"
"d[^def]o"
TableFilterTable(s) to importstringYes"SalesOrderDetail"
"SalesOrderHeader"
"%Order%"

"Store"
"Score"
"S_ore"
ImportOptionsObjects to exclude from importImportOptionsNoImportOptions.None
ImportOptions.ExcludeCheckConstraint
ImportOptions.ExcludeColumnDefault
ImportOptions.ExcludeForeignKey
ImportOptions.ExcludeIdentity
ImportOptions.ExcludeIndex
ImportOptions.ExcludePrimaryKey
ImportOptions.ExcludeUniqueKey
ImportOptions.ExcludeViews

Wildcards in Parameters

The SchemaFilter and TableFilter parameters can use wildcards. The wildcard characters allowed are the same as the ones used in the T-SQL LIKE statement:
The percent sign ( % ) for zero or more characters.
Underscore ( _ ) for a single character.
Brackets ( [ ] ) for a single character in a range or set.
Brackets ( [ ^ ] ) for a single character not in a range or set.

Please note: if your object names contain characters that are used as wildcards, for example underscores in table names, you can treat wildcards as literals by enclosing them in brackets. If you have two tables Product_Category and ProductXCategory and you pass the TableFilter parameter “Product_Category”, both tables will be returned because the underscore is treated as a wildcard. You must pass the parameter “Product[_]Category” to treat the underscore as a literal to only return the first table.

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.

Limitations

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.

Conclusion

This post is an introduction to the Biml extension methods ImportTableNodes and ImportDB. It shows how to use the methods by providing parameters with or without wildcards. The main difference between the two methods is that ImportTableNodes is restricted to importing one schema, while ImportDB can import everything in a database. ImportDB is being deprecated because of its limitations, performance issues and other quirks (not covered in this blog post), use GetDatabaseSchema instead.

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