By: Koen Verbeeck
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 how we can easily retrieve the database schema using an extension function.
Fetching Metadata
Biml has several extension functions available that help you 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.
