Stairway to Biml

Stairway to Biml Level 7 – Populating the Database for a Custom Biml Framework

,

Biml is a markup language that enables you to quickly represent a variety of database related models and constructs, including SSIS packages, models, permissions and more. This stairway helps you get started using the language to represent your objects.

This “mini-series” is adapted from three chapters in The Biml Book:

  • Chapter 6: A Custom Biml Framework
  • Chapter 7: Using Biml as an SSIS Design Patterns Engine
  • Chapter 8: Integration with a Custom SSIS Execution Framework

In this mini-series I am going to build a moderately complex solution: a custom Biml metadata-driven framework. I write moderately because everyone brings their own experience and comfort level with them to any project. Some will enjoy working with metadata; others will type a lot. Although the code I will build (and I hope that you will build this code also) in the demonstrations qualifies as “moderately complex,” the solution itself is a relatively simple example of a Biml Framework.

Note: BimlFlex is a Biml framework designed, built, and maintained by Varigence, the creators of Biml. BimlFlex is an example of a commercial, production-ready Biml framework.

The framework solution consists of a database that contains metadata – which we will build – and a couple Biml files that read and respond to that metadata. As with all solutions, ours is not the only way to build anything. We’re positive there are other ways to provide the same functionality, and we’re pretty sure some of those ways are better (such as BimlFlex).

The purpose of this article is to discuss and demonstrate one way to build a custom Biml Framework. Note that while we are directly using database tables to store our metadata, Biml provides an abstraction for metadata called Biml Metadata Models. To avoid introducing too much at once, we won’t use them here, but you can learn about Biml metadata models and how to use them for automation in the chapter in The Biml Book titled “Metadata Automation.”

For the demonstration purposes we are using Visual Studio 2017 and BimlExpress Build 5.0.63025.0. If you are using different versions of either Visual Studio (SQL Server Data Tools) or BimlExpress, your user experience may differ.

What is a Framework?

According to the dictionary integrated into Microsoft® Word a framework /'fram?w?rk/ is “a basic conceptional structure (as of ideas)” or “a skeletal, openwork, or structural frame.” A Biml Framework is a structure that contains the building blocks of an application. In this case, a data integration application that uses SSIS.

How does it work? Biml files contain BimlScript that connects to metadata stored in a database and use that metadata to construct Biml artifacts such as databases, schemas, tables, SSIS packages, and T-SQL scripts.

Why Use Metadata?

Business Intelligence Markup Language (Biml) often uses metadata as the source for data-related objects. Although metadata collection is beyond the scope of this article, there are several methods one can use to extract relational metadata from a database and some methods are covered in detail in the chapter in The Biml Book titled “Importing Metadata.”

Andy Note: I will share, however, that I use a rather clunky SSIS package similar to the package I describe in a blog post found at https://andyleonard.blog/2008/08/ssis-design-pattern-collect-enterprise-sql-server-database-metadata-with-ssis. The post is titled “SSIS Design Pattern - Collect Enterprise SQL Server Database Metadata with SSIS.” In fact, I used this very package as a starting point for my Biml Metadata Loader SSIS package. My Biml Metadata Loader SSIS package only works with SQL Server but can be easily modified to work with other database engines.

Regardless of which method you choose for storing metadata, you will need to persist it. Precisely which metadata will you need to store? It depends, which is the standard answer to any data-related question, equivalent to the answer “42” in “The Hitchhiker's Guide to the Galaxy.” (Adams, 1979).

I humbly submit you begin by storing metadata related to the Biml Relational Hierarchy because Biml Relational Hierarchy metadata is used by Biml to generate much of its output.

The Biml Relational Hierarchy

In this section I discuss and demonstrate the Biml Relational Hierarchy. The Biml Relational Hierarchy is at the heart of much of the automation I accomplish using Biml. It is a “Biml-izied” version of relational database objects:

  • Connections
  • Databases
  • Schemas
  • Tables
  • Columns (included in the Table Biml)

These database objects appear in the BimlStudio Logical View as shown in Figure 1:

Figure 1. Viewing the Biml Relational Hierarchy in Logical View

Although the objects are related in a hierarchy, they appear “flat” similar to the bulleted listing above. The flat representation is intentional and reveals one aspect of the relationship between the relational database objects as shown in Figure 2:

Figure 2. Viewing the “Flat” Aspect of Biml Relational Hierarchy Objects

One can address the Biml relational database objects programmatically by addressing them directly from the RootNode Object, for example:

var sourceConnection = RootNode.Connections["ContosoSource"];
var sourceDatabase = RootNode.Databases["ContosoOLTP"];
var sourceSchema = RootNode.Schemas["Contoso.dbo"];
var sourceTable = RootNode.Tables["dbo.Channel"];

Another aspect of the Biml Relational Hierarchy Objects is their hierarchical relationship to each other, which can be represented as shown in Figure 3:

Figure 3. The Biml Relational Hierarchy

Examining the Biml Relational Hierarchy Object’s Biml reveals the “keys” of the hierarchical relationship, shared between levels of the hierarchy:

Figure 4. Viewing the Relational Database Hierarchy in Biml

In Figure 4 we see a Connection named “ContosoSource” that contains a Database named “ContosoOLTP.” The “dbo” Schema resides in the “ContosoOLTP” database, and a Table named “Channel” is found in the “dbo” (named “ContosoOLTP.dbo”) schema.

Examining the Channel table Biml further, we see the Columns metadata as shown in Figure 5:

Figure 5. Viewing the Channel Table Biml

Figures 4 and 5 provide different, but equally valid, views of a populated Biml Relational Hierarchy.

In this section we discussed and demonstrated the Biml Relational Hierarchy. In the next section, let’s examine how we might persist the Biml Relational Hierarchy in a database.

Storing the Relational Biml Hierarchy Metadata in a Database

In The Biml Book chapter titled Importing Metadata, we examined some of the many ways to import Biml Relational Hierarchy metadata using methods exposed by the Varigence.Biml.CoreLowerer.SchemaManagement namespace. In this section we examine one example of how we might store Biml Relational Hierarchy metadata in a custom database for use within Biml projects. One reason we may want to store Biml Relational Hierarchy metadata is to dynamically map source columns to destination columns when the columns have different names and data types. Some things gained by describing the whole schema and mappings in a database rather than just doing it all with source schema discovery and cobbling together the mappings:

  • The database is reliable
  • We can do change detection if sources or targets schemas are modified
  • We can store additional metadata like Slowly Changing Dimension Type and late/early arrivals easily
  • We can specify mappings and have the database enforce integrity (so we avoid bogus mappings to missing columns)
  • We can add business logic to those mappings because we (now) have a place to store it

Creating a Database and Schema for Custom Biml Relational Hierarchy Metadata

To demonstrate, let’s create a database named “BRMetadata” to host our metadata using the following Transact-SQL statement:

CREATE DATABASE BRMetadata

Let’s next create a schema named “di” – for data integration – in the BRMetadata database using the following Transact-SQL statements:

Use BRMetadata
GO
CREATE SCHEMA di

Creating a Table for Custom Biml Relational Hierarchy Connections Metadata

Let’s now build custom tables to contain our Biml Relational Hierarchy metadata. We begin by creating a table to hold connections, constructed using the following Transact-SQL statement:

CREATE TABLE [di].[Connections]

CREATE TABLE   (
      ConnectionID int identity(1,1) Not NULL
         Constraint PK_Connections Primary Key
      , ConnectionName varchar(255) Not NULL
      , ConnectionString varchar(255) Not NULL
  )

We have a table to store Connections. It still has that new table smell. Awesome. Let’s add some metadata using the following Transact-SQL statement (Please note: Your Connection String VALUES will not match mine):

INSERT INTO [di].[Connections]
 (ConnectionName
 ,ConnectionString)
VALUES
 ('ContosoSource','Provider=SQLNCLI11;Data Source=vDemo\Demo;Integrated Security=SSPI;Initial Catalog=ContosoOLTP;')
,('ContosoTarget','Provider=SQLNCLI11;Data Source=vDemo\Demo;Integrated Security=SSPI;Initial Catalog=ContosoRetailDW;')

The Transact-SQL above inserts metadata for two connections into our BRMetadata.di.Connections table. The connections are named ContosoSource and ContosoTarget and they are aimed at the ContosoOLTP and ContosoRetailDW databases, respectively.

Creating a Table for Custom Biml Relational Hierarchy Database Metadata

Let’s continue by creating and populating a table to hold metadata for the next level in the Biml Relational Hierarchy, Databases. The following Transact-SQL statements build and insert records into the [di].[Databases] table:

CREATE TABLE [di].[Databases]
  (
        DatabaseID int identity(1,1) Not NULL
         Constraint PK_Databases Primary Key
      , ConnectionID int Not Null
         Constraint FK_Databases_Connections Foreign Key
          References [di].[Connections](ConnectionID)
      , DatabaseName varchar(255) Not NULL
  )
GO
INSERT INTO [di].[Databases]
(ConnectionID
,DatabaseName)
VALUES
 ((Select ConnectionID From [di].[Connections] Where ConnectionName = 'ContosoSource')
 ,'ContosoOLTP')
,((Select ConnectionID From [di].[Connections] Where ConnectionName = 'ContosoTarget')
 ,'ContosoRetailDW')

The Transact-SQL above inserts metadata for two databases into our BRMetadata.di.Databases table. The databases are named ContosoOLTP, which is related to the ContosoSource connection, and ContosoRetailDW, which is related to the ContosoTarget connection.

Creating a Table for Custom Biml Relational Hierarchy Schema Metadata

Let’s continue by creating and populating a table to hold metadata for the next level in the Biml Relational Hierarchy, Schemas. The following Transact-SQL statements build and insert records into the [di].[Schemas] table:

CREATE TABLE [di].[Schemas]
  (
        SchemaID int identity(1,1) Not NULL
         Constraint PK_Schemas Primary Key
      , DatabaseID int Not Null
         Constraint FK_Schemas_Databases Foreign Key
          References [di].[Databases](DatabaseID)
      , SchemaName varchar(255) Not NULL
  )
GO
INSERT INTO [di].[Schemas]
(DatabaseID
,SchemaName)
VALUES
 ((Select DatabaseID From [di].[Databases] Where DatabaseName = 'ContosoOLTP')
 ,'dbo')
,((Select DatabaseID From [di].[Databases] Where DatabaseName = 'ContosoRetailDW')
 ,'dbo')

The Transact-SQL above inserts metadata for two schemas into our BRMetadata.di.Schemas table. The schemas are both named dbo. One is related to the ContosoOLTP database, the other to the ContosoRetailDW database.

Creating a Table for Custom Biml Relational Hierarchy Tables Metadata

Let’s next create and populate a table to hold metadata for the level beneath Schemas in the Biml Relational Hierarchy, Tables. The following Transact-SQL statements build and insert records into the [di].[Tables] table:

CREATE TABLE [di].[Tables]
  (
        TableID int identity(1,1) Not NULL
         Constraint PK_Tables Primary Key
      , SchemaID int Not Null
         Constraint FK_Tables_Schemas Foreign Key
          References [di].[Databases](DatabaseID)
      , TableName varchar(255) Not NULL
  )
GO
INSERT INTO [di].[Tables]
(SchemaID
,TableName)
VALUES
 ((Select SchemaID From [di].[Schemas] Where DatabaseID = (Select DatabaseID From [di].[Databases] Where DatabaseName = 'ContosoOLTP'))
 ,'Channel')
,((Select SchemaID From [di].[Schemas] Where DatabaseID = (Select DatabaseID From [di].[Databases] Where DatabaseName = 'ContosoRetailDW'))
 ,'DimChannel')

The Transact-SQL above inserts metadata for two tables – ContosoOLTP.dbo.Channel and ContosoRetailDW.dbo.DimChannel into our BRMetadata.di.Tables table.

Creating a Table for Custom Biml Relational Hierarchy Columns Metadata

Let’s next create and populate a table to hold metadata for the lowest level of the Biml Relational Hierarchy, Columns. The following Transact-SQL statements build and insert records into the [di].[Columns] table:

CREATE TABLE [di].[Columns]
  (
        ColumnID int identity(1,1) Not NULL
         Constraint PK_Columns Primary Key
      , TableID int Not Null
         Constraint FK_Columns_Tables Foreign Key
          References [di].[Tables](TableID)
      , ColumnName varchar(255) Not NULL
      , DataType  varchar(255) Not NULL
      , [Length] int Not NULL
      , IsNullable bit Not NULL
      , IsIdentity bit Not NULL
  )
GO
INSERT INTO [di].[Columns]
(TableID
,ColumnName
,DataType
,[Length]
,IsNullable
,IsIdentity
)
VALUES
 ((Select TableID From [di].[Tables] Where TableName = 'Channel')
 ,'Label', 'String', 100, 0, 0)
,((Select TableID From [di].[Tables] Where TableName = 'Channel')
 ,'Name', 'String', 20, 1, 0)
,((Select TableID From [di].[Tables] Where TableName = 'Channel')
 ,'Description', 'String', 50, 1, 0)
,((Select TableID From [di].[Tables] Where TableName = 'Channel')
 ,'LastUpdatedDate', 'Date', 0, 1, 0)
,((Select TableID From [di].[Tables] Where TableName = 'DimChannel')
 ,'ChannelLabel', 'String', 100, 0, 0)
,((Select TableID From [di].[Tables] Where TableName = 'DimChannel')
 ,'ChannelName', 'String', 20, 1, 0)
,((Select TableID From [di].[Tables] Where TableName = 'DimChannel')
 ,'ChannelDescription', 'String', 50, 1, 0)
,((Select TableID From [di].[Tables] Where TableName = 'DimChannel')
 ,'UpdateDate', 'DateTime', 0, 1, 0)

The Transact-SQL above inserts metadata for columns in two tables – ContosoOLTP.dbo.Channel and ContosoRetailDW.dbo.DimChannel into our BRMetadata.di.Columns table. Please note: The data types we store are not SQL Server data types, they are Biml data types.

For more information about translating SQL Server and Biml data types, please see Cathrine Wilhelmsen’s excellent blog post titled “SQL Server, SSIS and Biml Data Types” at https://www.cathrinewilhelmsen.net/2014/05/27/sql-server-ssis-and-biml-data-types/

Creating a Table for Mappings Metadata

If you’ve done traditional ETL (Extract, Transform, and Load) work, you may be familiar with a mapping document. Many enterprises use Excel to create ETL mapping documentation and it often looks similar to that shown in Figure 6:

Figure 6. An ETL Mapping Document in Excel

The goal of the Mappings table is to “map” the columns from the Source to the Target. The BRMetadata.di.Mappings table pulls the Biml Relational Hierarchy metadata together to create the data equivalent of a mapping document. The Mappings table contains an identity column (MappingsID), and columns to hold the source and target ColumnID VALUES. The ColumnID VALUES will suffice because they reference their respective tables, which reference their respective schemas, which reference their respective databases, which reference their respective connections.

The following Transact-SQL statements build and insert records into the [di].[Mappings] table:

CREATE TABLE [di].[Mappings]
  (
        MappingID int identity(1,1) Not NULL
         Constraint PK_Mappings Primary Key
      , SourceColumnID int Not Null
         Constraint FK_Mappings_SourceColumns Foreign Key
          References [di].[Columns](ColumnID)
      , TargetColumnID int Not Null
         Constraint FK_Mappings_TargetColumns Foreign Key
          References [di].[Columns](ColumnID)
      , IsBusinessKey bit Not Null
         Constraint DF_Mappings_IsBusinessKey
          Default(0)
  )
INSERT INTO [di].[Mappings]
(SourceColumnID
,TargetColumnID
,IsBusinessKey)
VALUES
 (1, 5, 1)
,(2, 6, 0)
,(3, 7, 0)
,(4, 8, 0)

With Mappings metadata loaded, it’s possible to write a query that returns a dataset that appears remarkably similar to that found in the mapping document shown in Figure 5. In fact, let’s store this query in a view by executing the following Transact-SQL statement:

Create View [di].[metadataMappings]
As
 SELECT
 scn.ConnectionName As SourceConnectionName
, sd.DatabaseName As SourceDatabaseName
, ss.SchemaName As SourceSchemaName
, st.TableName As SourceTableName
, st.TableID As SourceTableID
, sc.ColumnName As SourceColumnName
, sc.ColumnID As SourceColumnID
, tcn.ConnectionName As TargetConnectionName
, td.DatabaseName As TargetDatabaseName
, ts.SchemaName As TargetSchemaName
, tt.TableName As TargetTableName
, tt.TableID As TargetTableID
, tc.ColumnName As TargetColumnName
, tc.ColumnID As TargetColumnID
, m.IsBusinessKey
From [di].[Mappings] As m
Join [di].Columns sc
  On sc.ColumnID = m.SourceColumnID
Join [di].[Tables] As st
  On st.TableID = sc.TableID
Join [di].[Schemas] As ss
  On ss.SchemaID = st.SchemaID
Join [di].[Databases] As sd
  On sd.DatabaseID = ss.DatabaseID
Join [di].[Connections] As scn
  On scn.ConnectionID = sd.ConnectionID
Join [di].Columns As tc
  On tc.ColumnID = m.TargetColumnID
Join [di].Tables As tt
  On tt.TableID = tc.TableID
Join [di].[Schemas] As ts
  On ts.SchemaID = tt.SchemaID
Join [di].[Databases] As td
  On td.DatabaseID = ts.DatabaseID
Join [di].[Connections] As tcn
  On tcn.ConnectionID = td.ConnectionID

Executing the Transact-SQL query “Select * From [di].[metadataMappings]” should return results similar to those shown in Figure 7:

Figure 7. Viewing Results of the Mapping Query

We now have enough Biml Relational Hierarchy metadata stored in our database to use in a Biml Project.

In this section we examined one example of how we might store Biml Relational Hierarchy metadata in a custom database for use within Biml projects. In the next section we demonstrate and discuss how we might use the metadata stored in our custom Biml Relational Hierarchy database to build Biml Relational Hierarchy objects in Biml.

Using the Relational Database Metadata to Build the Biml Relational Hierarchy

In this previous section we examined one example of storing Biml Relational Hierarchy metadata in a custom database. In this section we put our stored metadata to use! Open SQL Server Data Tools (SSDT) and create a new Integration Services project. You can name your SSIS project whatever you like, I named mine Chapter7.

As previously stated we are using Visual Studio 2017 and BimlExpress Build 5.0.63025.0. If you are using different versions of either Visual Studio (SQL Server Data Tools) or BimlExpress, your user experience may differ.

Add a new Biml file and rename it to LoadBimlRelationalHierarchy.biml as shown in Figure 8:

Figure 8. Viewing the Chapter7 Project and LoadBimlRelationalHierarchy.biml File

The LoadBimlRelationalHierarchy.biml file opens with default open and close Biml tags as shown in Figure 9:

Figure 9. Creating a New Biml File

We are going to need to access the BRMetadata database we designed and populated earlier, so let’s add import directives to use the .Net Framework’s System.Data namespace. When added, your code should appear as follows (with the newly-added code highlighted):

<#@ import namespace="System.Data" #>
<# string connectionString = @"Provider=SQLNCLI11;Data Source=vDemo\Demo;Integrated Security=SSPI;Initial Catalog=BRMetadata;"; #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
</Biml>

In addition to the directives, we declare and initialize a variable named connectionString that contains a connection string to our BRMetadata database. Please note: Your connection string will almost certainly differ.

Building Biml Connection Tags from Metadata

We next introduce more BimlScript to read our Connections metadata and use it to build Biml Connections. Add a BimlScript code block after the opening Biml tag that contains the following (highlighted) BimlScript:

<#@ import namespace="System.Data" #>
<# string connectionString = @"Provider=SQLNCLI11;Data Source=vDemo\Demo;Integrated Security=SSPI;Initial Catalog=BRMetadata;"; #>font>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <!-- Connections -->
  <# var dataTable = ExternalDataAccess.GetDataTable(connectionString, "Select ConnectionName, ConnectionString From [di].[Connections]"); #>
  <Connections>
    <# foreach (DataRow row in dataTable.Rows) { #>
    <Connection Name="<#=row["ConnectionName"]#>" ConnectionString="<#=row["ConnectionString"]#>" />
    <# } #>
  </Connections>
</Biml>

When added, your Biml file should appear similar to that shown in Figure 10:

Figure 10. Connections BimlScript Added

At Line 5 we use a built-in BimlScript utility method called ExternalDataAccess.GetDataTable, which handles all of the database access drudgery for us. We simply supply our connection string and our query and get back a DataTable with the query execution results. On line 6 we pop out of BimlScript and write a Biml Connections opening tag. On lines 7-9 we iterate over the rows in our dataTable and create a Biml connection for each, using the ConnectionName and ConnectionString columns to populate the Biml connection objects.

After saving the LoadBimlRelationalHierarchy.biml file, the Preview pane displays the Biml Connections, as shown in Figure 11:

Figure 11. Viewing the Preview with Biml Connection Tags

At this point in our demonstration, the LoadBimlRelationalHierarchy.biml file is generating valid Biml from metadata. How cool!

Building Biml Database Tags from Metadata

We can leverage the BimlScript code pattern we built for retrieving Biml Connection metadata from the BRMetadata database to retrieve Biml Database metadata. Add the highlighted portions shown below to retrieve Biml Database metadata:

<#@ import namespace="System.Data" #>
<# string connectionString = @"Provider=SQLNCLI11;Data Source=vDemo\Demo;Integrated Security=SSPI;Initial Catalog=BRMetadata;"; #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <!-- Connections -->
  <# var dataTable = ExternalDataAccess.GetDataTable(connectionString, "Select ConnectionName, ConnectionString From [di].[Connections]"); #>
  <Connections>
    <# foreach (DataRow row in dataTable.Rows) { #>
    <Connection Name="<#=row["ConnectionName"]#>" ConnectionString="<#=row["ConnectionString"]#>" />
    <# } #>
  </Connections>
  <!-- Databases -->
  <# dataTable = ExternalDataAccess.GetDataTable(connectionString, "Select d.DatabaseName, c.ConnectionName From [di].[Databases] As d Join [di].[Connections] As c On c.ConnectionID = d.ConnectionID"); #>
  <Databases>
    <# foreach (DataRow row in dataTable.Rows) { #>
      <Database Name="<#=row["DatabaseName"]#>" ConnectionName="<#=row ["ConnectionName"]#>" />
    <# } #>
  </Databases>
</Biml>

When added, your Biml file should appear similar to that shown in Figure 12:

Figure 12. Databases BimlScript Added

As with the Connections BimlScript, the Databases BimlScript calls the ExternalDataAccess.GetDataTable utility method at line 13 that queries the BRMetadata database and populates the dataTable variable with the results. On line 14 we jump out of BimlScript and write a Biml Databases opening tag. On Line 15 we start a foreach loop to iterate the rows returned to the dataTable variable. On line 16 we write the Biml for Database artifacts.

After saving the LoadBimlRelationalHierarchy.biml file, the Preview pane displays the Biml Databases, as shown in Figure 13:

Figure 13. Viewing the Preview with Biml Database Tags

Building Biml Schema Tags from Metadata

We can leverage the BimlScript code pattern we built for retrieving Biml Connection and Database metadata from the BRMetadata database to retrieve Biml Schema metadata. Add the highlighted portions shown below to retrieve Biml Schema metadata:

<#@ import namespace="System.Data" #>
<# string connectionString = @"Provider=SQLNCLI11;Data Source=vDemo\Demo;Integrated Security=SSPI;Initial Catalog=BRMetadata;"; #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <!-- Connections -->
  <# var dataTable = ExternalDataAccess.GetDataTable(connectionString, "Select ConnectionName, ConnectionString From [di].[Connections]"); #>
  <Connections>
    <# foreach (DataRow row in dataTable.Rows) { #>
    <Connection Name="<#=row["ConnectionName"]#>" ConnectionString="<#=row["ConnectionString"]#>" />
    <# } #>
  </Connections>
  <!-- Databases -->
  <# dataTable = ExternalDataAccess.GetDataTable(connectionString, "Select d.DatabaseName, c.ConnectionName From [di].[Databases] As d Join [di].[Connections] As c On c.ConnectionID = d.ConnectionID"); #>
  <Databases>
    <# foreach (DataRow row in dataTable.Rows) { #>
      <Database Name="<#=row["DatabaseName"]#>" ConnectionName="<#=row ["ConnectionName"]#>" />
    <# } #>
  </Databases>
  <!-- Schemas -->
  <# dataTable = ExternalDataAccess.GetDataTable(connectionString, "Select s.SchemaName, d.DatabaseName From [di].[Schemas] As s Join [di].[Databases] As d On d.DatabaseID = s.DatabaseID"); #>
  <Schemas>
    <# foreach (DataRow row in dataTable.Rows) { #>
      <Schema Name="<#=row["SchemaName"]#>" DatabaseName="<#=row ["DatabaseName"]#>" />
    <# } #>
  </Schemas>
</Biml>

When added, your Biml file should appear similar to that shown in Figure 14:

Figure 14. Schemas BimlScript Added

As with the Connections and Databases BimlScript, the Schemas BimlScript starts with a ExternalDataAccess.GetDataTable call to retrieve schemas metadata from the BRMetadata database on line 21. On Line 22 we jump out of BimlScript and write a Biml Schemas opening tag. On lines 23-25 we iterate the dataset in dataTable to write Biml Schema tags for each row returned by the query.

After saving the LoadBimlRelationalHierarchy.biml file, the Preview pane displays the Biml Schemas, as shown in Figure 15:

Figure 15. Viewing the Preview with Biml Schema Tags

Building Biml Table Tags from Metadata

With Tables, we depart from the BimlScript code pattern we built for retrieving Biml Connection, Database, and Schema metadata from the BRMetadata database to retrieve Biml Table metadata as shown in the highlighted portions shown below to retrieve Biml Table metadata:

<#@ import namespace="System.Data" #>
<# string connectionString = @"Provider=SQLNCLI11;Data Source=vDemo\Demo;Integrated Security=SSPI;Initial Catalog=BRMetadata;"; #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <!-- Connections -->
  <# var dataTable = ExternalDataAccess.GetDataTable(connectionString, "Select ConnectionName, ConnectionString From [di].[Connections]"); #>
  <Connections>
    <# foreach (DataRow row in dataTable.Rows) { #>
    <Connection Name="<#=row["ConnectionName"]#>" ConnectionString="<#=row["ConnectionString"]#>" />
    <# } #>
  </Connections>
  <!-- Databases -->
  <# dataTable = ExternalDataAccess.GetDataTable(connectionString, "Select d.DatabaseName, c.ConnectionName From [di].[Databases] As d Join [di].[Connections] As c On c.ConnectionID = d.ConnectionID"); #>
  <Databases>
    <# foreach (DataRow row in dataTable.Rows) { #>
      <Database Name="<#=row["DatabaseName"]#>" ConnectionName="<#=row ["ConnectionName"]#>" />
    <# } #>
  </Databases>
  <!-- Schemas -->
  <# dataTable = ExternalDataAccess.GetDataTable(connectionString, "Select s.SchemaName, d.DatabaseName From [di].[Schemas] As s Join [di].[Databases] As d On d.DatabaseID = s.DatabaseID"); #>
  <Schemas>
    <# foreach (DataRow row in dataTable.Rows) { #>
      <Schema Name="<#=row["SchemaName"]#>" DatabaseName="<#=row ["DatabaseName"]#>" />
    <# } #>
  </ Schemas >
  <!-- Tables -->
<# var tableQuery = @"
Select distinct mm.SourceDatabaseName + '.' + mm.SourceSchemaName As SourceQualifiedSchemaName
  , mm.SourceSchemaName, mm.SourceTableName, mm.SourceTableID, mm.SourceTableName
  , mm.TargetDatabaseName + '.' + mm.TargetSchemaName As TargetQualifiedSchemaName
  , mm.TargetSchemaName, mm.TargetTableName, mm.TargetTableID
From [di].[metadataMappings] As mm;";
  dataTable = ExternalDataAccess.GetDataTable(connectionString, tableQuery); #>
      <Tables>
        <# foreach(DataRow row in dataTable.Rows) { #>
        <# var sourceTableId = row["SourceTableID"].ToString(); #>
        <# var targetTableId = row["TargetTableID"].ToString(); #>
        <Table Name="<#=row["SourceTableName"]#>" SchemaName="<#=row["SourceQualifiedSchemaName"]#>">
          <Annotations>
            <Annotation Tag="MappedTableID"><#=targetTableId#></Annotation>
            <Annotation Tag="MappedTableName"><#=row["TargetTableName"]#></Annotation>
          </Annotations>
        </Table>
        <Table Name="<#=row["TargetTableName"]#>" SchemaName="<#=row["TargetQualifiedSchemaName"]#>">
          <Annotations>
            <Annotation Tag="MappedTableID"><#=sourceTableId#></Annotation>
            <Annotation Tag="MappedTableName"><#=row["SourceTableName"]#></Annotation>
          </Annotations>
        </Table>
        <# } #>
    </Tables>
</Biml>

When added, your Biml file should appear similar to that shown in Figure 16:

Figure 16. Tables BimlScript Added

On lines 29-34 we declare and initialize a variable (tableQuery) to contain our Transact-SQL query that retrieves Table metadata from the BRMetadata database. On line 35 we call the utility method ExternalDataAccess.GetDataTable, passing it the connectionString and tableQuery variables. On line 37 we begin a foreach loop to iterate the resulting dataset. On lines 38-39 we declare and initialize two variables, sourceTableId and targetTableId. On lines 40-45 we build the Source Tables returned to the dataTable variable. On lines 46-51 we build the Target Tables returned to the dataTable variable. On lines 41-44 and 47-50, we add Biml Annotations that contain two tags: MappedTableID and MappedTableName.

You can think of Biml Annotations as being like extended properties or custom attributes you add to Biml artifacts. We use Biml Annotations here to store mapping attributes for Biml Tables. In the next subsection we will use Biml Annotations to store mapping attributes for business keys, which are VALUES that uniquely identify rows in both the source and target tables. If you plan to dive into Biml, do yourself a favor and get very comfortable using Biml annotations.

We will rely on these Biml Annotations when we build SSIS Packages in the next section.

After saving the LoadBimlRelationalHierarchy.biml file, the Preview pane displays the Biml Tables, as shown in Figure 17:

Figure 17. Viewing the Preview with Biml Table Tags

Building Biml Column Tags from Metadata

We can leverage the BimlScript code pattern we built for retrieving Biml Connection, Database, Schema, and Table metadata from the BRMetadata database to retrieve Biml Column metadata. There is a twist however: Biml Columns metadata is embedded within Biml Tables. Add the highlighted portions shown below to retrieve Biml Column metadata:

    <#@ import namespace="System.Data" #>
    <# string connectionString = @"Provider=SQLNCLI11;Data Source=vDemo\Demo;Integrated Security=SSPI;Initial Catalog=BRMetadata;"; #>
    <Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <!-- Connections -->
      <# var dataTable = ExternalDataAccess.GetDataTable(connectionString, "Select ConnectionName, ConnectionString From [di].[Connections]"); #>
      <Connections>
        <# foreach (DataRow row in dataTable.Rows) { #>
        <Connection Name="<#=row["ConnectionName"]#>" ConnectionString="<#=row["ConnectionString"]#>" />
        <# } #>
      </Connections>
      <!-- Databases -->
      <# dataTable = ExternalDataAccess.GetDataTable(connectionString, "Select d.DatabaseName, c.ConnectionName From [di].[Databases] As d Join [di].[Connections] As c On c.ConnectionID = d.ConnectionID"); #>
      <Databases>
        <# foreach (DataRow row in dataTable.Rows) { #>
          <Database Name="<#=row["DatabaseName"]#>" ConnectionName="<#=row ["ConnectionName"]#>" />
        <# } #>
      </Databases>
      <!-- Schemas -->
      <# dataTable = ExternalDataAccess.GetDataTable(connectionString, "Select s.SchemaName, d.DatabaseName From [di].[Schemas] As s Join [di].[Databases] As d On d.DatabaseID = s.DatabaseID"); #>
      <Schemas>
        <# foreach (DataRow row in dataTable.Rows) { #>
          <Schema Name="<#=row["SchemaName"]#>" DatabaseName="<#=row ["DatabaseName"]#>" />
        <# } #>
      </Schemas>
      <!-- Tables -->
    <# var tableQuery = @"
    Select distinct mm.SourceDatabaseName + '.' + mm.SourceSchemaName As SourceQualifiedSchemaName
      , mm.SourceSchemaName, mm.SourceTableName, mm.SourceTableID, mm.SourceTableName
      , mm.TargetDatabaseName + '.' + mm.TargetSchemaName As TargetQualifiedSchemaName
      , mm.TargetSchemaName, mm.TargetTableName, mm.TargetTableID
    From [di].[metadataMappings] As mm;";
    var columnsQuery = @"
    Select mm.SourceTableID, mm.SourceColumnName
      , sc.DataType As SourceDataType, sc.[Length] As SourceLength
      , sc.IsNullable As SourceIsNullable, mm.TargetTableID, mm.TargetTableName
      , mm.TargetColumnName, tc.DataType As TargetDataType
      , tc.[Length] As TargetLength, tc.IsNullable As TargetIsNullable
      , mm.IsBusinessKey
    From [di].[metadataMappings] As mm
    Join [di].[Columns] sc
      On sc.ColumnID = mm.SourceColumnID
    Join [di].[Columns] tc
      On tc.ColumnID = mm.TargetColumnID ";
      dataTable = ExternalDataAccess.GetDataTable(connectionString, tableQuery);
      var colTable = ExternalDataAccess.GetDataTable(connectionString, columnsQuery); #>
      <Tables>
        <# foreach(DataRow row in dataTable.Rows) { #>
        <# var sourceTableId = row["SourceTableID"].ToString(); #>
        <# var targetTableId = row["TargetTableID"].ToString(); #>
        <Table Name="<#=row["SourceTableName"]#>" SchemaName="<#=row["SourceQualifiedSchemaName"]#>">
          <Columns>
            <# foreach(var scr in colTable.Rows.OfType<DataRow>().Where(r => r["SourceTableID"].ToString()==sourceTableId)) { #>
            <Column Name="<#=scr["SourceColumnName"]#>"
                    DataType="<#=scr["SourceDataType"]#>"
                    Length="<#=scr["SourceLength"]#>"
                    IsNullable="<#=scr["SourceIsNullable"]#>">
              <Annotations>
                <Annotation Tag="IsBusinessKey"><#=scr["IsBusinessKey"]#></Annotation>
                <Annotation Tag="MappedColumnName"><#=scr["TargetColumnName"]#></Annotation>
              </Annotations>
            </Column>
            <# } #>
          </Columns>
          <Annotations>
            <Annotation Tag="MappedTableID"><#=targetTableId#></Annotation>
            <Annotation Tag="MappedTableName"><#=row["TargetTableName"]#></Annotation>
          </Annotations>
        </Table>
        <Table Name="<#=row["TargetTableName"]#>" SchemaName="<#=row["TargetQualifiedSchemaName"]#>">
          <Columns>
            <# foreach(var scr in colTable.Rows.OfType<DataRow>().Where(r => r["TargetTableID"].ToString()==targetTableId)) { #>
            <Column Name="<#=scr["TargetColumnName"]#>"
                    DataType="<#=scr["TargetDataType"]#>"
                    Length="<#=scr["TargetLength"]#>"
                    IsNullable="<#=scr["TargetIsNullable"]#>">
              <Annotations>
                <Annotation Tag="IsBusinessKey"><#=scr["IsBusinessKey"]#></Annotation>
                <Annotation Tag="MappedColumnName"><#=scr["SourceColumnName"]#></Annotation>
              </Annotations>
            </Column>
            <# } #>
          </Columns>
          <Annotations>
            <Annotation Tag="MappedTableID"><#=sourceTableId#></Annotation>
            <Annotation Tag="MappedTableName"><#=row["SourceTableName"]#></Annotation>
          </Annotations>
        </Table>
        <# } #>
      </Tables>
    </Biml>

Please note that there are four locations where Biml and BimlScript are added.

When added, your Biml file should appear similar to that shown in Figures 18a and 18b (shown in two figures so the code is legible):

Figure 18a. Top Half with Columns BimlScript Added

Figure 18b. Bottom Half with Columns BimlScript Added

To build the Columns metadata we insert four snippets of Biml and BimlScript. The first snippet declares and initializes a variable named columnsQuery on lines 36-47. The second snippet of BimlScript is the declaration and initialization of the colTable variable on line 50, which calls ExternalDataAccess.GetDataTable to populate the variable with Columns metadata from the BRMetadata database. After declaring and initializing variables to contain sourceTableId and targetTableId on lines 53 and 54, we iterate the Source table column in the third BimlScript snippet using a foreach loops and some Language Integrated Query (Linq) to first filter for Source columns in lines 57-67. We use Annotations to store column attributes for business keys and the mapped Target Column Name on lines 63 and 64. We repeat the Columns metadata iteration – with a Linq filter for Target Columns – in our fourth snippet of BimlScript on lines 76-86. We use Annotations to store column attributes for business keys and the mapped Source Column Name on lines 82 and 83. After the business key and mapped Column Name Biml Annotations we close the Biml Column tags on lines 68 and 85, respectively.

After saving the LoadBimlRelationalHierarchy.biml file, the Preview pane displays the Biml Columns, as shown in Figures 19a and 19b:

Figure 19a. Viewing the Preview with Biml Column Tags

Figure 19b. Viewing the Preview with Biml Column Tags

A good way to test your Biml and BimlScript is to build or expand the Biml file. In Solution Explorer, right click the LoadBimlRelationalHierarchy.biml file and click “Generate SSIS Packages.” If your Biml or BimlScript contains an error, it may be displayed in a dialog; the error will definitely be displayed in the Output window. If all goes well, your SSDT Output window will display Biml Compiler Output indicating the “Biml expansion completed” as shown in Figure 20:

Figure 20. Viewing the Biml Compiler Output in the SSDT Output Window

In this section we used BimlScript to retrieve metadata from the BRMetadata database to build and populate the Biml Relational Hierarchy. In our next section, we leverage the Biml Relational Hierarchy and use the Mappings metadata to build a simple SSIS Package.

Conclusion

Business Intelligence Markup Language (Biml) often uses metadata to source data-related objects. In this chapter we built a relatively simple custom Biml metadata database and one Biml file that reads the metadata. In our next article we finish this first step in building our solution by adding another Biml file that responds to that metadata.

This article is part of the parent stairway Stairway to Biml

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

Share

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

Related content