Loading lesson map...

Biml Basics for Relational Databases

Why Model Databases in Biml?

Manually creating staging tables is tedious and error-prone:

A 50-table source system requires 50 CREATE TABLE statements
Column changes in the source require manual updates everywhere
Inconsistent naming conventions creep in across tables
Deploying schema changes across Dev/Test/Prod is manual work

Biml solves this with metadata-driven table generation:

Import source schema once, generate all staging tables automatically
Add audit columns (LoadDate, SourceSystem) to every table with one template
Regenerate DDL instantly when source schemas change
Deploy consistent schemas across all environments

Time savings: A 100-table staging layer that takes days to build manually can be generated in minutes with Biml.

Quick Start: Import an Existing Database

This example imports tables from a SQL Server database and creates staging table definitions with audit columns:

<#@ template language="C#" tier="10" #>
<#@ import namespace="Varigence.Biml.CoreLowerer.SchemaManagement" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <OleDbConnection Name="Source"
      ConnectionString="Provider=SQLNCLI11;Server=localhost;
      Initial Catalog=AdventureWorks;Integrated Security=SSPI;" />
  </Connections>

  <#
  var importResult = ExternalDataAccess.GetDatabaseSchema(
    RootNode.Connections["Source"],
    new[] { "Sales", "Production" },  // Schemas to import
    null                               // All tables
  );
  #>

  <Databases>
    <Database Name="Staging" ConnectionName="Source" />
  </Databases>

  <Tables>
    <# foreach (var table in importResult.TableNodes) { #>
    <Table Name="STG_<#=table.Name#>" SchemaName="Staging.[default]">
      <Columns>
        <# foreach (var col in table.Columns) { #>
        <Column Name="<#=col.Name#>"
          DataType="<#=col.DataType#>"
          Length="<#=col.Length#>"
          IsNullable="true" />
        <# } #>
        <!-- Audit columns added to every table -->
        <Column Name="ETL_LoadDateTime" DataType="DateTime" />
        <Column Name="ETL_SourceSystem" DataType="String" Length="50" />
      </Columns>
    </Table>
    <# } #>
  </Tables>
</Biml>

Result: One build creates staging table definitions for all imported tables, each with standardized audit columns.

Core Concepts

Connections

Define database connections to use throughout your Biml project:

<Connections>
  <OleDbConnection Name="SourceDB"
    ConnectionString="Provider=SQLNCLI11;Server=localhost;
    Initial Catalog=AdventureWorks;Integrated Security=SSPI;" />
</Connections>

Common Connection Types:

Type	Use Case
`OleDbConnection`	SQL Server, most databases (recommended for SSIS)
`AdoNetConnection`	.NET data providers
`OdbcConnection`	ODBC drivers
`OracleConnection`	Oracle (uses Attunity in SSIS)
`TeradataConnection`	Teradata (uses Attunity in SSIS)

Databases and Schemas

Model your target database structure:

<Databases>
  <Database Name="Staging" ConnectionName="SourceDB" />
</Databases>

<Schemas>
  <Schema Name="stg" DatabaseName="Staging" />
</Schemas>

Every database automatically has a default schema. Reference it as DatabaseName.[default].

Tables and Columns

Define tables with columns, data types, and constraints:

<Tables>
  <Table Name="Customer" SchemaName="Staging.[default]">
    <Columns>
      <Column Name="CustomerID" DataType="Int32" IsNullable="false" />
      <Column Name="CustomerName" DataType="String" Length="100" />
      <Column Name="Balance" DataType="Decimal" Precision="18" Scale="2" />
      <Column Name="CreatedDate" DataType="DateTime" />
    </Columns>
  </Table>
</Tables>

Column Attributes

Define column properties to accurately model your database schema.

Common Attributes

Attribute	Type	Purpose	Example
`Name`	String	Column identifier	`CustomerID`
`DataType`	Enum	SQL data type	`Int32`, `String`, `DateTime`, `Decimal`
`Length`	Int	String/binary length (-1 for MAX)	`50` for VARCHAR(50)
`Precision`	Int	Total digits for decimal	`18`
`Scale`	Int	Decimal places	`2` for (18,2)
`IsNullable`	Boolean	Allow NULL values	`true`, `false`
`IdentitySeed`	Long	Identity start value	`1`
`IdentityIncrement`	Long	Identity increment	`1`

Data Type Mappings

Biml DataType	SQL Server	Use Case
`Int32`	INT	Standard integers
`Int64`	BIGINT	Large integers
`String`	VARCHAR/NVARCHAR	Text (use Length)
`AnsiString`	VARCHAR	Non-Unicode text
`Decimal`	DECIMAL	Precise numbers (use Precision, Scale)
`DateTime`	DATETIME	Legacy dates
`DateTime2`	DATETIME2	Modern dates (recommended)
`Boolean`	BIT	True/false
`Binary`	VARBINARY	Binary data
`Currency`	MONEY	Financial values
`Guid`	UNIQUEIDENTIFIER	GUIDs

Example: Complete Column Definitions

<Table Name="Customer" SchemaName="Sales.dbo">
  <Columns>
    <!-- Identity column -->
    <Column Name="CustomerID" DataType="Int32" IdentitySeed="1" IdentityIncrement="1"/>

    <!-- String with length -->
    <Column Name="FirstName" DataType="String" Length="50" IsNullable="false"/>
    <Column Name="LastName" DataType="String" Length="50" IsNullable="false"/>
    <Column Name="Email" DataType="String" Length="255" IsNullable="true"/>

    <!-- Decimal with precision/scale -->
    <Column Name="CreditLimit" DataType="Decimal" Precision="18" Scale="2" IsNullable="true"/>

    <!-- Date columns -->
    <Column Name="CreatedDate" DataType="DateTime2" IsNullable="false"/>
    <Column Name="ModifiedDate" DataType="DateTime2" IsNullable="true"/>
  </Columns>
</Table>

SCD Type Attribute (Data Warehousing)

For dimension tables, the ScdType attribute indicates Slowly Changing Dimension handling:

ScdType	Behavior	Example Columns
`0`	Fixed attribute (never changes)	CustomerID, AccountNumber
`1`	Overwrite (keep current only)	Email, Phone
`2`	Track history (add new row)	Address, MaritalStatus
`3`	Keep previous and current	Limited use, stores one prior value

<Columns>
  <Column Name="CustomerID" DataType="Int32" ScdType="0"/>
  <Column Name="Email" DataType="String" Length="255" ScdType="1"/>
  <Column Name="Address" DataType="String" Length="200" ScdType="2"/>
</Columns>

Note: ScdType is metadata used by BimlFlex and custom templates; standard Biml compilation doesn't automatically generate SCD logic.

Importing Database Metadata

GetDatabaseSchema

The most flexible method for importing existing database structures:

var importResult = ExternalDataAccess.GetDatabaseSchema(
  RootNode.Connections["Source"],  // Connection to use
  new[] { "Sales", "HumanResources" },  // Schemas to include
  null,  // Tables to include (null = all)
  ImportOptions.ExcludeForeignKey | ImportOptions.ExcludeIndex
);

// Access imported objects
foreach (var table in importResult.TableNodes) {
  // table.Name, table.Columns, etc.
}

Import Options

Control what gets imported:

Option	Effect
`ExcludeForeignKey`	Skip FK constraints (common for staging)
`ExcludeIndex`	Skip indexes
`ExcludePrimaryKey`	Skip PK constraints
`ExcludeViews`	Import tables only
`ExcludeIdentity`	Remove identity specification
`ExcludeCheckConstraint`	Skip check constraints
`ExcludeColumnDefault`	Skip default values

Combine options with |: ImportOptions.ExcludeForeignKey | ImportOptions.ExcludeIndex

Alternative Methods

For simpler scenarios:

ImportDB(connection, schemaFilter, tableFilter) - Full database import with wildcard filters (%)
ImportTableNodes(connection, schema, tableFilter) - Single schema import

Generating DDL Scripts

Generate CREATE TABLE scripts from your Biml table definitions:

<#@ template language="C#" tier="20" #>
<# foreach (var table in RootNode.Tables) { #>
-- DDL for <#=table.Name#>
<#=table.GetDropAndCreateDdl()#>
GO

<# } #>

This outputs platform-appropriate DDL based on your connection type.

Keys and Indexes

Primary and Unique Keys

<Table Name="Customer" SchemaName="Staging.[default]">
  <Columns>
    <Column Name="CustomerID" DataType="Int32" IsNullable="false" IdentityIncrement="1" />
    <Column Name="CustomerKey" DataType="String" Length="20" IsNullable="false" />
    <Column Name="CustomerName" DataType="String" Length="100" />
  </Columns>
  <Keys>
    <PrimaryKey Name="PK_Customer">
      <Columns>
        <Column ColumnName="CustomerID" />
      </Columns>
    </PrimaryKey>
    <UniqueKey Name="UK_Customer_Key">
      <Columns>
        <Column ColumnName="CustomerKey" />
      </Columns>
    </UniqueKey>
  </Keys>
</Table>

Indexes

<Indexes>
  <Index Name="IX_Customer_Name">
    <Columns>
      <Column ColumnName="CustomerName" />
    </Columns>
  </Index>
</Indexes>

Table References (Foreign Keys)

Model relationships between tables using TableReference for foreign keys.

Basic Foreign Key

<Table Name="Order" SchemaName="Sales.dbo">
  <Columns>
    <Column Name="OrderID" DataType="Int32" IdentitySeed="1" IdentityIncrement="1"/>
    <Column Name="OrderDate" DataType="DateTime2"/>
    <TableReference Name="CustomerID" TableName="Sales.Customer"/>
  </Columns>
</Table>

The TableReference creates a foreign key relationship to the primary key of the referenced table.

Dimension/Fact Table Pattern

For data warehouse designs, use TableReference to model dimension relationships:

<!-- Dimension Table -->
<Table Name="DimCustomer" SchemaName="DW.dbo">
  <Columns>
    <Column Name="CustomerKey" DataType="Int32" IdentitySeed="1" IdentityIncrement="1"/>
    <Column Name="CustomerID" DataType="Int32"/>
    <Column Name="CustomerName" DataType="String" Length="100"/>
  </Columns>
  <Keys>
    <PrimaryKey Name="PK_DimCustomer">
      <Columns><Column ColumnName="CustomerKey"/></Columns>
    </PrimaryKey>
  </Keys>
</Table>

<!-- Fact Table with Dimension References -->
<Table Name="FactSales" SchemaName="DW.dbo">
  <Columns>
    <Column Name="SalesKey" DataType="Int64" IdentitySeed="1" IdentityIncrement="1"/>
    <TableReference Name="CustomerKey" TableName="DW.DimCustomer"/>
    <TableReference Name="ProductKey" TableName="DW.DimProduct"/>
    <TableReference Name="DateKey" TableName="DW.DimDate">
      <ReferenceColumns>
        <ReferenceColumn ColumnName="DateKey"/>
      </ReferenceColumns>
    </TableReference>
    <Column Name="Quantity" DataType="Int32"/>
    <Column Name="Amount" DataType="Decimal" Precision="18" Scale="2"/>
  </Columns>
</Table>

Staging Tables (Skip FK Creation)

For staging tables, skip FK constraint creation since referential integrity isn't enforced:

<TableReference Name="CustomerID" TableName="Staging.[default].Customer"
  ForeignKeyConstraintMode="DoNotCreate" />

Benefits for Automation

When you define TableReference relationships:

BimlScript can traverse relationships (table.Columns.OfType<AstTableReferenceNode>())
Generate DDL with proper foreign key constraints
Create data flow mappings that respect referential integrity
Build incremental load sequences based on dependencies

Common Patterns

Staging Layer with Audit Columns

Add standard audit columns to every imported table:

<# foreach (var sourceTable in importResult.TableNodes) { #>
<Table Name="STG_<#=sourceTable.Name#>" SchemaName="Staging.[default]">
  <Columns>
    <# foreach (var col in sourceTable.Columns) { #>
    <Column Name="<#=col.Name#>"
      DataType="<#=col.DataType#>"
      Length="<#=col.Length#>"
      Precision="<#=col.Precision#>"
      Scale="<#=col.Scale#>"
      IsNullable="true" />
    <# } #>
    <!-- Standard audit columns -->
    <Column Name="ETL_LoadDateTime" DataType="DateTime" IsNullable="false" />
    <Column Name="ETL_SourceSystem" DataType="String" Length="100" />
    <Column Name="ETL_BatchID" DataType="Int64" />
  </Columns>
</Table>
<# } #>

Excluding Foreign Keys for Staging

Staging tables typically don't need FK constraints:

var importResult = ExternalDataAccess.GetDatabaseSchema(
  RootNode.Connections["Source"],
  new[] { "Sales" },
  null,
  ImportOptions.ExcludeForeignKey | ImportOptions.ExcludeIndex | ImportOptions.ExcludeIdentity
);

Data Vault Hash Keys

For Data Vault implementations, use HashedKey columns:

<Columns>
  <Column Name="CustomerKey" DataType="String" Length="50" IsNullable="false" />
  <Column Name="CustomerName" DataType="String" Length="100" />
  <HashedKey Name="HK_Customer" ConstraintName="UK_CustomerKey" />
</Columns>
<Keys>
  <UniqueKey Name="UK_CustomerKey">
    <Columns>
      <Column ColumnName="CustomerKey" />
    </Columns>
  </UniqueKey>
</Keys>

Automation Benefits

Time Savings

Approach	Time for 50-Table Staging Layer
Manual DDL creation	25-50 hours
Biml metadata import	~30 minutes
Time saved	98%

Consistency Benefits

Every table gets identical audit columns
Naming conventions enforced automatically
Data type mappings standardized across all tables
Changes propagate instantly on rebuild

CI/CD Integration

Generate DDL as part of your build pipeline:

# Azure DevOps example
- task: MSBuild@1
 displayName: 'Generate Staging DDL'
 inputs:
  solution: '**/*.mst'
  msbuildArguments: '/p:OutputPath=$(Build.ArtifactStagingDirectory)/DDL'

Beyond SSIS: Cloud Platform Support

While this guide focuses on SQL Server with BimlExpress and BimlStudio, BimlFlex extends relational modeling to cloud platforms:

Azure Data Factory - Generate linked services and datasets
Databricks - Generate Delta Lake DDL and notebooks
Snowflake - Generate stages, tables, and stored procedures
Microsoft Fabric - Generate lakehouse tables and pipelines

For cloud platform automation, see the BimlFlex documentation.

Next Steps

Now that you understand relational modeling in Biml:

Biml for SSIS - Generate data flow packages using your table definitions
Common Patterns - Reusable patterns for staging, incremental loads, and error handling
Build Automated Staging Layer - Complete end-to-end staging example
Troubleshooting Guide - Debug import and generation issues

Why Model Databases in Biml?​

Quick Start: Import an Existing Database​

Core Concepts​

Connections​

Databases and Schemas​

Tables and Columns​

Column Attributes​

Common Attributes​

Data Type Mappings​

Example: Complete Column Definitions​

SCD Type Attribute (Data Warehousing)​

Importing Database Metadata​

GetDatabaseSchema​

Import Options​

Alternative Methods​

Generating DDL Scripts​

Keys and Indexes​

Primary and Unique Keys​

Indexes​

Table References (Foreign Keys)​

Basic Foreign Key​

Dimension/Fact Table Pattern​

Staging Tables (Skip FK Creation)​

Benefits for Automation​

Common Patterns​

Staging Layer with Audit Columns​

Excluding Foreign Keys for Staging​

Data Vault Hash Keys​

Automation Benefits​

Time Savings​

Consistency Benefits​

CI/CD Integration​

Beyond SSIS: Cloud Platform Support​

Next Steps​

Reference Documentation​