Overview
BimlScript gets really interesting when you can use metadata to generate packages. You can, for example, read the source table and destination table details from a metadata repository and create an SSIS package for each entry. In this chapter, we take a look at how we can easily retrieve the database schema using an extension function.
Explanation
Fetching Metadata
Biml has several extension functions available to help with your development. One of those functions is the GetDatabaseSchema function. This function will read the entire database schema for a given connection. You can also pass parameters to exclude schemas or tables. Furthermore, you can also specify if views, column defaults, foreign keys, etc. need to be included or not.
Let’s try to read the database schema for the AdventureWorks database.
First, we need to create a connection object, which we call SourceConnection. Then we call the GetDatabaseSchema function without any parameters. This will read all of the metadata objects from the database. We can verify this by displaying the schema and table name in an XML comment. The preview pane displays the full list of tables:
The GetDatabaseSchema function can also handle input parameters. Let’s only read the metadata for the HumanResources schema and skip certain metadata objects such as foreign keys and column defaults:
With the ImportOptions, you can control the behavior of the GetDatabaseSchema function. You can find more info about them in the blog post Biml Extension Methods: GetDatabaseSchema.
Other Methods
GetDatabaseSchema isn’t the only option to read metadata into Biml. There are similar methods: ImportTableNodes and ImportDB. ImportTableNodes can only import metadata from the tables of one schema. ImportDB is similar to GetDatabaseSchema, but uses wildcards instead of lists to restrict metadata. However, the flexibility and performance of GetDatabaseSchema makes the function the preferred choice.
Other options are to enter metadata manually somewhere, for example in an Excel file, a table or in Master Data Services. Using C# functionality, you just read the data that you need into Biml. When you need metadata from a relational database, such as SQL Server, you can use metadata views like sys.columns (from SQL Server) or INFORMATION_SCHEMA.COLUMNS (supported by different database vendors). A detailed treatment of all of these options is outside the scope of this tutorial.
In the next chapter we are going to use the results of GetDatabaseSchema to create relational objects inside Biml.
Additional Information
- The script used in this chapter can be downloaded here.
Koen Verbeeck is a seasoned business intelligence consultant with over a decade of experience with the Microsoft Data Platform. He holds several certifications, including Azure Data Engineer. He’s a prolific writer, with over 375 articles on technologies such as Microsoft Fabric, SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at various events such as PASS, SQLBits, dataMinds Connect and many others. He frequently delivers educational webinars on MSSQLTips.com. For his efforts, Koen has been awarded the Microsoft MVP data platform award for many years.
- MSSQLTips Awards:
- Leadership Award (200+ Tips) – 2021
- Author of the Year – 2014/2020/2022
- Author Contender – 2024/2025
