Skip to main content

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:

TypeUse Case
OleDbConnectionSQL Server, most databases (recommended for SSIS)
AdoNetConnection.NET data providers
OdbcConnectionODBC drivers
OracleConnectionOracle (uses Attunity in SSIS)
TeradataConnectionTeradata (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​

AttributeTypePurposeExample
NameStringColumn identifierCustomerID
DataTypeEnumSQL data typeInt32, String, DateTime, Decimal
LengthIntString/binary length (-1 for MAX)50 for VARCHAR(50)
PrecisionIntTotal digits for decimal18
ScaleIntDecimal places2 for (18,2)
IsNullableBooleanAllow NULL valuestrue, false
IdentitySeedLongIdentity start value1
IdentityIncrementLongIdentity increment1

Data Type Mappings​

Biml DataTypeSQL ServerUse Case
Int32INTStandard integers
Int64BIGINTLarge integers
StringVARCHAR/NVARCHARText (use Length)
AnsiStringVARCHARNon-Unicode text
DecimalDECIMALPrecise numbers (use Precision, Scale)
DateTimeDATETIMELegacy dates
DateTime2DATETIME2Modern dates (recommended)
BooleanBITTrue/false
BinaryVARBINARYBinary data
CurrencyMONEYFinancial values
GuidUNIQUEIDENTIFIERGUIDs

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:

ScdTypeBehaviorExample Columns
0Fixed attribute (never changes)CustomerID, AccountNumber
1Overwrite (keep current only)Email, Phone
2Track history (add new row)Address, MaritalStatus
3Keep previous and currentLimited 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:

OptionEffect
ExcludeForeignKeySkip FK constraints (common for staging)
ExcludeIndexSkip indexes
ExcludePrimaryKeySkip PK constraints
ExcludeViewsImport tables only
ExcludeIdentityRemove identity specification
ExcludeCheckConstraintSkip check constraints
ExcludeColumnDefaultSkip 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​

ApproachTime for 50-Table Staging Layer
Manual DDL creation25-50 hours
Biml metadata import~30 minutes
Time saved98%

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:

Reference Documentation​