Biml Extension Methods: ImportTableNodes and ImportDB

Updated: The content in this post has been hidden by default because ImportDB is slow, buggy and will therefore be deprecated. Use GetDatabaseSchema instead.

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

This post is an introduction to ImportTableNodes and ImportDB (which 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 AdventureWorks2014 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 :)

9 thoughts on “Biml Extension Methods: ImportTableNodes and ImportDB”

Har nettopp brukt en uke på å få a) Attunity til å virke i BIML og b) bruke det til å laste tabeller fra Evry til vårt datavarehus. Jeg er overbevist om at jeg skal kunne spare inn millioner av kroner tiltenkt konsulenter for å lage lastejobber de neste årene. Jeg er totalt frelst.

Thanks for a most useful post. I really appreciated your advice on how to handle the underscore problem.

I’m stuck on a problem in my current project where I need to exclude 13 tables out of 150. I know its not a big deal to just delete those, but its a pain having to remember to delete them. Is there any way I can set the table filter to exclude a table based on tableFilter parameters?

Hi Paul. Please take a look at my post about GetDatabaseSchema. By using GetDatabaseSchema instead of ImportDB you will be able to specify which tables to include. It should to solve your problem :)

Pingback: Mist – A tool for BIML » Business Intelligence Solutions - Amol Pandey

Hi. Great post. I have a question about ImportOptions.ExcludeViews . I am using code very similar to yours, mine works but if I try to remove the option ImportOptions.ExcludeViews I get no results, if I leave it in I get a lot of results. My understanding is this option is preventing me from receiving metadata related to views (which I DO want to get). How can I modify I line like the following so that it will allow me to get metadata about views?

var importResult = sourceConnection.ImportDB(“”, “”, ImportOptions.ExcludeForeignKey | ImportOptions.ExcludeColumnDefault | ImportOptions.ExcludeViews);

Hi Tim. You should be able to use the following code to get metadata about both tables and views:
var importResult = sourceConnection.ImportDB(“”, “”, ImportOptions.ExcludeForeignKey | ImportOptions.ExcludeColumnDefault);
Is this the code you have tried to use that returns no results?

Hi, I am using ExternalDataAccess.GetDataTable to execute the query but it gives me DataTable which does not support GetBiml() method. can I execute query using ImportTableNodes ? I dont have single view or table to investigate I have query that has joins. please help.

Hi Cathrine,

I want to import from a source only a view (It is a dynamic mapping table, and I want to loop over it in foreach Control Nuggets). I tried to use ImportDB() but it sounds to be not possible. How can I do that? Is it also possible if we want to excute the query directly in Biml and store it in a table object and then loop over this table?

Thanks!

Hi Ziad, check out GetDatabaseSchema instead, it’s a little more flexible :) https://www.cathrinewilhelmsen.net/2015/07/12/biml-extension-methods-getdatabaseschema/

Share Your Thoughts?

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