Build Automated Staging Layer

Introduction

A staging layer serves as the first landing zone for source data in your data warehouse architecture. Automating its creation eliminates repetitive manual coding and ensures consistency across your data pipelines.

What You Will Build

This guide walks you through creating an automated staging layer solution that:

• Imports metadata from source systems automatically
• Generates DDL for staging tables across multiple platforms
• Creates data loading pipelines for consistent extraction patterns
• Supports multiple platforms: SSIS, Azure Data Factory, Databricks, Snowflake, and Microsoft Fabric

Time Savings

Approach	Time per Table	100 Tables	500 Tables
Manual coding	30-60 min	50-100 hours	250-500 hours
BimlScript automation	2-5 min setup	15 min total	20 min total

Once your BimlScript solution is built, adding new tables requires only updating your metadata source—the automation handles the rest.

Prerequisites

Required Tools

Choose one of the following based on your target platform:

Tool	Best For	Platforms Supported
BimlExpress	SSIS development (free)	SSIS only
BimlStudio	Advanced SSIS with IDE	SSIS, SSAS
BimlFlex	Cloud platforms	ADF, Databricks, Snowflake, Fabric, SSIS

Knowledge Requirements

Before starting, ensure familiarity with:

• XML Basics - Understanding elements, attributes, and nesting
• C# Primer - Variables, loops, and LINQ basics
• BimlScript Fundamentals - Code nuggets and tiers

Core Concepts

Metadata-Driven Development

BimlScript uses metadata as the single source of truth. Instead of writing repetitive code, you:

1. Define metadata - Describe your tables, columns, and connections once
2. Write patterns - Create reusable templates that consume metadata
3. Generate output - Produce DDL, packages, or pipelines automatically

File Organization and Tier System

BimlScript files compile in a specific order controlled by tiers. Lower-numbered tiers compile first:

tier="0" → Environment configuration (connections, databases)
tier="1" → Metadata import (table definitions)
tier="2" → Output generation (packages, DDL, pipelines)

Staging Layer Design Principles

Effective staging layers follow these rules:

• Non-persistent temporary storage - Data is truncated and reloaded each cycle
• No business transformations - Raw source data only; transformations happen downstream
• Match source attributes - Column names and types mirror the source system
• One target per source - Each source table maps to exactly one staging table
• Add audit columns - Track load timestamps, process names, and row counts

Implementation Pattern

All platforms follow the same four-step pattern:

┌─────────────────────┐
│ 1. Environment      │  Define connections, databases, schemas
├─────────────────────┤
│ 2. Metadata Import  │  Read source tables, generate staging definitions
├─────────────────────┤
│ 3. DDL Deployment   │  Create/recreate staging tables
├─────────────────────┤
│ 4. Data Loading     │  Extract from source, load to staging
└─────────────────────┘

SSIS Implementation

This section provides a complete four-file solution for SQL Server Integration Services.

File Naming Convention

Use numbered prefixes to indicate which build step includes each file:

File	Step 1	Step 2	Purpose
1-2-Environment.biml	Include	Include	Connections and configuration
1-2-CreateTableMetadata.biml	Include	Include	Table definitions
1-x-DeployTargetTables.biml	Include	Skip	DDL deployment package
x-2-CreateLoadPackages.biml	Skip	Include	Data loading packages

File 1: Environment Configuration

This file defines your source and target connections. Modify only the connection strings when retargeting to different environments.

1-2-Environment.biml

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <!-- Source system connection -->
    <Connections>
        <OleDbConnection
            Name="Source"
            ConnectionString="Provider=SQLNCLI11;Server=localhost;Initial Catalog=AdventureWorks2012;Integrated Security=SSPI;">
            <Annotations>
                <Annotation AnnotationType="Tag" Tag="Environment">Development</Annotation>
            </Annotations>
        </OleDbConnection>

        <!-- Staging database connection -->
        <OleDbConnection
            Name="Target"
            ConnectionString="Provider=SQLNCLI11;Server=localhost;Initial Catalog=StagingDB;Integrated Security=SSPI;" />
    </Connections>

    <!-- Target database and schema definitions -->
    <Databases>
        <Database Name="StagingDB" ConnectionName="Target" />
    </Databases>

    <Schemas>
        <Schema Name="stg" DatabaseName="StagingDB" Owner="dbo" />
    </Schemas>
</Biml>

Key elements:

• OleDbConnection - Database connections with provider-specific connection strings
• Annotations - Custom metadata tags for environment-specific logic
• Schema - Target schema for staging tables

File 2: Table Metadata Import

This file imports source table metadata and creates staging table definitions with audit columns.

1-2-CreateTableMetadata.biml

<#@ template tier="1" #>
<#
    // Import source database metadata
    var sourceConnection = RootNode.DbConnections["Source"];
    var importResult = sourceConnection.ImportDB(
        "", // Schema filter (empty = all schemas)
        "", // Table filter (empty = all tables)
        ImportOptions.ExcludeForeignKey |
        ImportOptions.ExcludeColumnDefault |
        ImportOptions.ExcludeViews |
        ImportOptions.ExcludeIdentity
    );
#>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Tables>
        <# foreach (var table in importResult.TableNodes) { #>
        <Table Name="stg_<#=table.Schema.Name#>_<#=table.Name#>" SchemaName="StagingDB.stg">
            <Columns>
                <!-- Import source columns with type conversion -->
                <# foreach (var column in table.Columns) { #>
                <# if (column.DataType == System.Data.DbType.AnsiString) { #>
                <!-- Convert ANSI to Unicode for consistency -->
                <Column Name="<#=column.Name#>" DataType="String" Length="<#=column.Length#>">
                    <Annotations>
                        <Annotation AnnotationType="Tag" Tag="ConvertToUnicode">yes</Annotation>
                    </Annotations>
                </Column>
                <# } else if (column.DataType == System.Data.DbType.AnsiStringFixedLength) { #>
                <Column Name="<#=column.Name#>" DataType="StringFixedLength" Length="<#=column.Length#>">
                    <Annotations>
                        <Annotation AnnotationType="Tag" Tag="ConvertToUnicode">yes</Annotation>
                    </Annotations>
                </Column>
                <# } else { #>
                <#=column.GetBiml()#>
                <# } #>
                <# } #>

                <!-- Audit columns -->
                <Column Name="LoadDateTime" DataType="DateTime" />
                <Column Name="ProcessName" DataType="String" Length="255" />
                <Column Name="SourceRowCount" DataType="Int32" IsNullable="true" />
            </Columns>

            <Annotations>
                <Annotation AnnotationType="Tag" Tag="SourceSchemaQualifiedName"><#=table.SchemaQualifiedName#></Annotation>
                <Annotation AnnotationType="Tag" Tag="SourceSchema"><#=table.Schema.Name#></Annotation>
                <Annotation AnnotationType="Tag" Tag="SourceTable"><#=table.Name#></Annotation>
            </Annotations>
        </Table>
        <# } #>
    </Tables>
</Biml>

Key elements:

• tier="1" - Ensures this compiles after environment (tier 0 default)
• ImportDB - Reads source metadata with filtering options
• ImportOptions - Excludes foreign keys, defaults, views, and identity specs
• Audit columns - Added to every staging table for tracking

File 3: DDL Deployment Package

This file generates an SSIS package that creates all staging tables.

1-x-DeployTargetTables.biml

<#@ template tier="2" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Packages>
        <Package Name="Deploy_StagingTables" ConstraintMode="Parallel" ProtectionLevel="DontSaveSensitive">
            <Variables>
                <Variable Name="ExecutionStart" DataType="DateTime" EvaluateAsExpression="true">GETDATE()</Variable>
            </Variables>

            <Tasks>
                <# foreach (var table in RootNode.Tables) { #>
                <ExecuteSQL Name="Create_<#=table.Name#>" ConnectionName="Target">
                    <DirectInput><![CDATA[
-- Drop existing table if present
IF OBJECT_ID('<#=table.SchemaQualifiedName#>', 'U') IS NOT NULL
    DROP TABLE <#=table.SchemaQualifiedName#>;

-- Create staging table
<#=table.GetTableDdl()#>
                    ]]></DirectInput>
                </ExecuteSQL>
                <# } #>
            </Tasks>
        </Package>
    </Packages>
</Biml>

Key elements:

• ConstraintMode="Parallel" - Tables created concurrently for faster deployment
• GetTableDdl() - Generates CREATE TABLE statement from Biml metadata
• CDATA - Preserves SQL formatting in the output

File 4: Data Loading Packages

This file generates SSIS packages that load data from source to staging.

x-2-CreateLoadPackages.biml

<#@ template tier="2" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Packages>
        <!-- Master orchestration package -->
        <Package Name="Master_LoadStaging" ConstraintMode="Linear" ProtectionLevel="DontSaveSensitive">
            <Variables>
                <Variable Name="ExecutionStart" DataType="DateTime" EvaluateAsExpression="true">GETDATE()</Variable>
                <Variable Name="ProcessName" DataType="String">Master_LoadStaging</Variable>
            </Variables>

            <Tasks>
                <# foreach (var table in RootNode.Tables) { #>
                <ExecutePackage Name="Load_<#=table.Name#>">
                    <ExternalProjectPackage Package="Load_<#=table.Name#>.dtsx" />
                </ExecutePackage>
                <# } #>
            </Tasks>
        </Package>

        <!-- Individual load packages -->
        <# foreach (var table in RootNode.Tables) { #>
        <Package Name="Load_<#=table.Name#>" ProtectionLevel="DontSaveSensitive">
            <Variables>
                <Variable Name="RowCount" DataType="Int32">0</Variable>
                <Variable Name="ProcessName" DataType="String">Load_<#=table.Name#></Variable>
            </Variables>

            <Tasks>
                <!-- Truncate staging table -->
                <ExecuteSQL Name="Truncate_<#=table.Name#>" ConnectionName="Target">
                    <DirectInput>TRUNCATE TABLE <#=table.SchemaQualifiedName#></DirectInput>
                </ExecuteSQL>

                <!-- Load data from source -->
                <Dataflow Name="Load_<#=table.Name#>">
                    <PrecedenceConstraints>
                        <Inputs>
                            <Input OutputPathName="Truncate_<#=table.Name#>.Output" />
                        </Inputs>
                    </PrecedenceConstraints>

                    <Transformations>
                        <!-- Source query -->
                        <OleDbSource Name="Source" ConnectionName="Source">
                            <DirectInput>
SELECT <#=table.GetColumnList(c => !c.Name.StartsWith("Load") && !c.Name.StartsWith("Process") && !c.Name.StartsWith("Source"))#>
FROM <#=table.GetTag("SourceSchemaQualifiedName")#>
                            </DirectInput>
                        </OleDbSource>

                        <!-- Data type conversions for Unicode -->
                        <# var columnsToConvert = table.Columns.Where(c => c.GetTag("ConvertToUnicode") == "yes").ToList(); #>
                        <# if (columnsToConvert.Any()) { #>
                        <DataConversion Name="ConvertToUnicode">
                            <Columns>
                                <# foreach (var column in columnsToConvert) { #>
                                <Column SourceColumn="<#=column.Name#>" TargetColumn="cvt_<#=column.Name#>" DataType="String" Length="<#=column.Length#>" />
                                <# } #>
                            </Columns>
                        </DataConversion>
                        <# } #>

                        <!-- Add audit columns -->
                        <DerivedColumns Name="AddAuditColumns">
                            <Columns>
                                <Column Name="LoadDateTime" DataType="DateTime">GETDATE()</Column>
                                <Column Name="ProcessName" DataType="String" Length="255">@[User::ProcessName]</Column>
                            </Columns>
                        </DerivedColumns>

                        <!-- Row count for logging -->
                        <RowCount Name="CountRows" VariableName="User.RowCount" />

                        <!-- Destination -->
                        <OleDbDestination Name="Destination" ConnectionName="Target">
                            <TableOutput TableName="<#=table.SchemaQualifiedName#>" />
                            <Columns>
                                <# foreach (var column in table.Columns.Where(c => !c.Name.StartsWith("Source"))) { #>
                                <# var sourceCol = columnsToConvert.Any(cc => cc.Name == column.Name) ? "cvt_" + column.Name : column.Name; #>
                                <# if (!column.Name.StartsWith("Load") && !column.Name.StartsWith("Process")) { #>
                                <Column SourceColumn="<#=sourceCol#>" TargetColumn="<#=column.Name#>" />
                                <# } #>
                                <# } #>
                            </Columns>
                        </OleDbDestination>
                    </Transformations>
                </Dataflow>

                <!-- Update row count in staging table -->
                <ExecuteSQL Name="UpdateRowCount_<#=table.Name#>" ConnectionName="Target">
                    <DirectInput>
UPDATE <#=table.SchemaQualifiedName#>
SET SourceRowCount = ?
WHERE LoadDateTime = (SELECT MAX(LoadDateTime) FROM <#=table.SchemaQualifiedName#>)
                    </DirectInput>
                    <Parameters>
                        <Parameter Name="0" VariableName="User.RowCount" DataType="Int32" />
                    </Parameters>
                    <PrecedenceConstraints>
                        <Inputs>
                            <Input OutputPathName="Load_<#=table.Name#>.Output" />
                        </Inputs>
                    </PrecedenceConstraints>
                </ExecuteSQL>
            </Tasks>
        </Package>
        <# } #>
    </Packages>
</Biml>

Key elements:

• Master package orchestrates individual load packages
• Truncate before load ensures idempotent execution
• DataConversion handles ANSI to Unicode conversion
• DerivedColumns adds audit tracking
• RowCount captures metrics for logging

Building the SSIS Solution

Step 1: Build DDL deployment

1. Select these files in BimlExpress/BimlStudio:

   • 1-2-Environment.biml
   • 1-2-CreateTableMetadata.biml
   • 1-x-DeployTargetTables.biml

2. Right-click and select Generate SSIS Packages

3. Execute Deploy_StagingTables.dtsx to create your staging tables

Step 2: Build load packages

1. Select these files:

   • 1-2-Environment.biml
   • 1-2-CreateTableMetadata.biml
   • x-2-CreateLoadPackages.biml

2. Right-click and select Generate SSIS Packages

3. Execute Master_LoadStaging.dtsx to load your staging data

Azure Data Factory Implementation

This section generates Azure Data Factory pipelines using BimlScript patterns.

File 1: ADF Environment Configuration

1-2-AdfEnvironment.biml

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <!-- Source connection metadata -->
    <Connections>
        <OleDbConnection Name="Source"
            ConnectionString="Provider=SQLNCLI11;Server=source-server.database.windows.net;Initial Catalog=SourceDB;Integrated Security=SSPI;">
            <Annotations>
                <Annotation AnnotationType="Tag" Tag="AdfLinkedServiceType">AzureSqlDatabase</Annotation>
                <Annotation AnnotationType="Tag" Tag="AdfServerName">source-server.database.windows.net</Annotation>
                <Annotation AnnotationType="Tag" Tag="AdfDatabaseName">SourceDB</Annotation>
                <Annotation AnnotationType="Tag" Tag="AdfConnectionString">Server=tcp:source-server.database.windows.net,1433;Initial Catalog=SourceDB;Encrypt=True;TrustServerCertificate=False;</Annotation>
            </Annotations>
        </OleDbConnection>

        <OleDbConnection Name="Target"
            ConnectionString="Provider=SQLNCLI11;Server=target-server.database.windows.net;Initial Catalog=StagingDB;Integrated Security=SSPI;">
            <Annotations>
                <Annotation AnnotationType="Tag" Tag="AdfLinkedServiceType">AzureSqlDatabase</Annotation>
                <Annotation AnnotationType="Tag" Tag="AdfServerName">target-server.database.windows.net</Annotation>
                <Annotation AnnotationType="Tag" Tag="AdfDatabaseName">StagingDB</Annotation>
                <Annotation AnnotationType="Tag" Tag="AdfConnectionString">Server=tcp:target-server.database.windows.net,1433;Initial Catalog=StagingDB;Encrypt=True;TrustServerCertificate=False;</Annotation>
            </Annotations>
        </OleDbConnection>
    </Connections>

    <Databases>
        <Database Name="StagingDB" ConnectionName="Target" />
    </Databases>

    <Schemas>
        <Schema Name="stg" DatabaseName="StagingDB" />
    </Schemas>
</Biml>

File 2: ADF Table Metadata

Use the same 1-2-CreateTableMetadata.biml from the SSIS section.

File 3: ADF Linked Services Generator

This file generates linked services using native Biml elements—the same approach used for SSIS connections.

1-x-AdfLinkedServices.biml

<#@ template tier="2" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <LinkedServices>
        <# foreach (var connection in RootNode.DbConnections) { #>
        <# var linkedServiceType = connection.GetTag("AdfLinkedServiceType"); #>

        <# if (linkedServiceType == "AzureSqlDatabase") { #>
        <AzureSqlDatabase Name="<#=connection.Name#>_LinkedService"
                          ConnectionString="<#=connection.GetTag("AdfConnectionString")#>" />
        <# } else if (linkedServiceType == "AzureBlobStorage") { #>
        <AzureBlobStorage Name="<#=connection.Name#>_LinkedService"
                          ConnectionString="<#=connection.GetTag("AdfConnectionString")#>" />
        <# } else if (linkedServiceType == "AzureDataLakeStoreGen2") { #>
        <AzureDataLakeStoreGen2 Name="<#=connection.Name#>_LinkedService"
                                Url="<#=connection.GetTag("AdfStorageUrl")#>" />
        <# } #>
        <# } #>
    </LinkedServices>
</Biml>

Key elements:

• <LinkedServices> - Container for all ADF linked service definitions
• <AzureSqlDatabase> - Azure SQL Database connection with connection string
• <AzureBlobStorage> - Azure Blob Storage with account key or SAS authentication
• <AzureDataLakeStoreGen2> - Data Lake Gen2 with URL-based authentication
• Connection metadata flows from annotations defined in File 1

File 3.5: ADF Datasets Generator

Datasets define the structure of data sources and sinks. Copy activities reference these datasets.

1-x-AdfDatasets.biml

<#@ template tier="2" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Datasets>
        <# foreach (var table in RootNode.Tables) { #>
        <!-- Source dataset for <#=table.GetTag("SourceSchemaQualifiedName")#> -->
        <AzureSqlTable Name="Source_<#=table.GetTag("SourceTable")#>"
                       LinkedServiceName="Source_LinkedService"
                       Schema="<#=table.GetTag("SourceSchema")#>"
                       Table="<#=table.GetTag("SourceTable")#>" />

        <!-- Target dataset for <#=table.SchemaQualifiedName#> -->
        <AzureSqlTable Name="Target_<#=table.Name#>"
                       LinkedServiceName="Target_LinkedService"
                       Schema="stg"
                       Table="<#=table.Name#>" />
        <# } #>
    </Datasets>
</Biml>

Key elements:

• <Datasets> - Container for all ADF dataset definitions
• <AzureSqlTable> - References a table in Azure SQL Database
• LinkedServiceName - Points to the linked service connection
• Source datasets use table.GetTag() to retrieve source metadata from File 2
• Target datasets reference the staging schema and generated table names

File 4: ADF Pipeline Generator

This file generates ADF pipelines using native Biml elements. Compare this to the SSIS File 4—both follow the same pattern of iterating RootNode.Tables.

x-2-AdfPipelines.biml

<#@ template tier="2" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Pipelines>
        <!-- Master orchestration pipeline -->
        <Pipeline Name="Master_LoadStaging" AdfFolder="Staging">
            <Activities>
                <# foreach (var table in RootNode.Tables) { #>
                <ExecutePipeline Name="Load_<#=table.Name#>"
                                 PipelineName="Load_<#=table.Name#>"
                                 WaitOnCompletion="true" />
                <# } #>
            </Activities>
        </Pipeline>

        <!-- Individual load pipelines (one per staging table) -->
        <# foreach (var table in RootNode.Tables) { #>
        <Pipeline Name="Load_<#=table.Name#>" AdfFolder="Staging/LoadPipelines">
            <Activities>
                <!-- Truncate staging table before loading -->
                <Script Name="Truncate_<#=table.Name#>" LinkedServiceName="Target_LinkedService">
                    <Scripts>
                        <Script Type="NonQuery"><![CDATA[TRUNCATE TABLE [stg].[<#=table.Name#>]]]></Script>
                    </Scripts>
                </Script>

                <!-- Copy data from source to staging -->
                <Copy Name="Copy_<#=table.Name#>">
                    <Dependencies>
                        <Dependency Condition="Succeeded" DependsOnActivityName="Truncate_<#=table.Name#>" />
                    </Dependencies>

                    <!-- Source configuration -->
                    <AzureSqlSource DatasetName="Source_<#=table.GetTag("SourceTable")#>">
                        <Query><![CDATA[SELECT <#=table.GetColumnList(c => !c.Name.StartsWith("Load") && !c.Name.StartsWith("Process") && !c.Name.StartsWith("Source"))#>
FROM <#=table.GetTag("SourceSchemaQualifiedName")#>]]></Query>
                    </AzureSqlSource>

                    <!-- Sink configuration -->
                    <AzureSqlSink DatasetName="Target_<#=table.Name#>" WriteBatchSize="10000" />
                </Copy>
            </Activities>
        </Pipeline>
        <# } #>
    </Pipelines>
</Biml>

Key elements:

• <Pipelines> - Container for all ADF pipeline definitions
• <Pipeline> - A single pipeline with name and optional folder organization
• <ExecutePipeline> - Orchestration activity that calls child pipelines
• <Script> - Executes SQL against a linked service (truncate operation)
• <Copy> - Data movement activity with source and sink
• <Dependencies> - Defines activity execution order
• <AzureSqlSource> / <AzureSqlSink> - SQL-specific source and sink types

Pattern comparison with SSIS:

SSIS Element	ADF Element	Purpose
<Package>	<Pipeline>	Container for data flow logic
<ExecutePackage>	<ExecutePipeline>	Orchestration / child execution
<ExecuteSQL>	<Script>	Run SQL commands (e.g., TRUNCATE)
<Dataflow>	<Copy>	Data movement
<OleDbSource>	<AzureSqlSource>	Read from database
<OleDbDestination>	<AzureSqlSink>	Write to database

Building the ADF Solution

Step 1: Build linked services and datasets

1. Select these files in BimlStudio:

   • 1-2-AdfEnvironment.biml
   • 1-2-CreateTableMetadata.biml
   • 1-x-AdfLinkedServices.biml
   • 1-x-AdfDatasets.biml

2. Build the project to generate ARM templates

Step 2: Build pipelines

1. Select these files:

   • 1-2-AdfEnvironment.biml
   • 1-2-CreateTableMetadata.biml
   • 1-x-AdfDatasets.biml
   • x-2-AdfPipelines.biml

2. Build the project to generate pipeline ARM templates

Step 3: Deploy to Azure

BimlStudio generates ARM templates that can be deployed via:

# Deploy using Azure CLI
az deployment group create \
    --resource-group MyResourceGroup \
    --template-file output/azuredeploy.json \
    --parameters factoryName=MyDataFactory

# Or deploy using Azure PowerShell
New-AzResourceGroupDeployment `
    -ResourceGroupName MyResourceGroup `
    -TemplateFile output/azuredeploy.json `
    -factoryName MyDataFactory

For CI/CD integration, see the CI/CD Integration section below.

Automation Benefits

Time Savings Comparison

Task	Manual Approach	BimlScript Automation
Create 1 staging table	15-30 min	0 min (generated)
Create 100 staging tables	25-50 hours	15 min setup
Add new source table	30-60 min	Update metadata, rebuild
Change column type	Find all references, update each	Update pattern, rebuild all
Add audit column to all tables	Hours of repetitive edits	1 line change, rebuild

Consistency Benefits

• Naming conventions - Enforced automatically by patterns
• Data types - Consistent mapping from source to target
• Audit columns - Present on every table, same structure
• Error handling - Same patterns across all packages

Scalability

BimlScript scales linearly:

• 10 tables: Same effort as 1,000 tables
• Adding a new source: Copy environment file, change connection string
• Platform migration: Keep metadata, change output patterns

Error Handling Patterns

SSIS: Row Redirection

<OleDbDestination Name="Destination" ConnectionName="Target" ErrorRowDisposition="RedirectRow">
    <TableOutput TableName="<#=table.SchemaQualifiedName#>" />
    <ErrorHandling>
        <ErrorOutput>
            <Transformations>
                <FlatFileDestination Name="ErrorRows" ConnectionName="ErrorFile">
                    <FileOutput />
                </FlatFileDestination>
            </Transformations>
        </ErrorOutput>
    </ErrorHandling>
</OleDbDestination>

Logging Pattern (All Platforms)

// Add to your BimlScript patterns
<#
    void LogExecution(string tableName, string status, int rowCount) {
        var logEntry = $"{DateTime.Now:yyyy-MM-dd HH:mm:ss}|{tableName}|{status}|{rowCount}";
        File.AppendAllText(@"C:\Logs\staging_execution.log", logEntry + Environment.NewLine);
    }
#>

Try-Catch Pattern (Databricks/Fabric)

try:
    # Load operation
    df.write.format("delta").mode("append").saveAsTable(target)
    log_success(table_name, row_count)
except Exception as e:
    log_failure(table_name, str(e))
    # Optionally re-raise or continue with next table
    raise

CI/CD Integration

Version Control Strategy

Structure your BimlScript repository:

/biml-staging/
├── /environments/
│   ├── dev-environment.biml
│   ├── test-environment.biml
│   └── prod-environment.biml
├── /patterns/
│   ├── create-table-metadata.biml
│   ├── deploy-tables.biml
│   └── load-packages.biml
├── /output/
│   └── (generated artifacts)
└── build.ps1

Build Automation (PowerShell)

# build.ps1
param(
    [Parameter(Mandatory=$true)]
    [ValidateSet('dev','test','prod')]
    [string]$Environment
)

$bimlStudioPath = "C:\Program Files\Varigence\BimlStudio\BimlStudio.exe"
$projectPath = ".\BimlStaging.mst"

# Copy environment-specific config
Copy-Item ".\environments\$Environment-environment.biml" ".\1-2-Environment.biml" -Force

# Build step 1: DDL deployment
& $bimlStudioPath /build $projectPath /step:1

# Build step 2: Load packages
& $bimlStudioPath /build $projectPath /step:2

Write-Host "Build complete for $Environment environment"

Azure DevOps Pipeline

trigger:
  branches:
    include:
      - main
      - develop

pool:
  vmImage: 'windows-latest'

variables:
  bimlStudioPath: 'C:\Program Files\Varigence\BimlStudio\BimlStudio.exe'

stages:
- stage: Build
  jobs:
  - job: BuildBiml
    steps:
    - task: PowerShell@2
      inputs:
        filePath: 'build.ps1'
        arguments: '-Environment $(Build.SourceBranchName)'

    - task: PublishPipelineArtifact@1
      inputs:
        targetPath: 'output'
        artifact: 'biml-artifacts'

- stage: Deploy
  dependsOn: Build
  condition: eq(variables['Build.SourceBranch'], 'refs/heads/main')
  jobs:
  - deployment: DeployToDev
    environment: 'development'
    strategy:
      runOnce:
        deploy:
          steps:
          - task: SqlDacpacDeployment@1
            inputs:
              # Deploy DDL scripts

Troubleshooting

Common Issues

Issue	Cause	Solution
"Connection not found"	Missing environment file in build	Ensure environment file is included in build step
Empty table list	ImportDB returns no tables	Check connection string and permissions
Type conversion errors	Unsupported source data type	Add explicit mapping in GetBiml() switch statement
Tier ordering issues	Files compile out of order	Verify tier numbers (0 → 1 → 2)

Debugging Tips

1. Preview generated Biml - Use BimlStudio's Preview Pane to see expanded output
2. Enable logging - Add <#@ output extension=".log" #> to trace execution
3. Check RootNode - Output RootNode.Tables.Count to verify metadata loaded
4. Validate connections - Test connection strings independently before building

Getting Help

• BimlScript Troubleshooting Guide
• Common Patterns Reference

Next Steps

After building your staging layer:

1. Add incremental loading - See Common Patterns for CDC patterns
2. Build transformation layer - Apply business logic downstream
3. Add data quality checks - Validate row counts and data integrity
4. Implement monitoring - Track load times and failure rates

Related Documentation

• Introduction to BimlScript
• Automation and Patterns
• BimlFlex for ADF