BimlScript Walkthroughs

Community-contributed step-by-step tutorials covering Biml, BimlScript, and the SSIS, SSAS, and Azure workloads they automate. Each section below collects walkthroughs on a single theme; pick a section that matches what you are trying to do and browse the entries.

Introduction & Concepts

Overviews of what Biml is, why it exists, and the small first-package exercises that anchor everything else.

• 8 Practical Biml Tips: Eight practical tips for working with Biml in Visual Studio, including editor settings, directive placement, escaping characters, and learning paths.
• Beginning with Biml: Understand what Biml describes, how to install it, and the five small exercises that build a working foundation for SSIS automation.
• Biml Basics Overview: Walk through the first three exercises every Biml learner should complete: a blank package, a project connection, and a truncate-and-reload staging package.
• Biml Introduction Session Webinar Content: Introductory webinar covering BimlStudio, BimlScript syntax, and the fundamentals needed for more advanced Biml topics.
• Building a Common ETL Framework with Biml: How a metadata-driven Biml project replaces hand-built SSIS templates and keeps every package aligned with a single ETL framework.
• Building a Data Warehouse While Source Systems Evolve: Combine source metadata alerts, a Biml automation framework, and a simple SSIS testing harness to keep a data warehouse in sync with source systems still in development.
• Creating a Basic Package Using Biml: Walk through the steps of authoring a small Biml file and expanding it into an SSIS package using the Biml Package Generator in BIDS Helper.
• Getting Started With Biml: Use a single Biml file to declare a connection and an SSIS package with one or more Execute SQL Tasks, then generate the package directly from the markup.
• Getting Started with BimlScript, Part 1: Use BimlExpress to create a Biml file, and then use it to generate an SSIS package in SSDT.
• Getting Started with BimlScript, Part 2: Add BimlScript code nuggets to a Biml file so the same template can auto generate multiple SSIS packages.
• Getting Started with BimlScript, Part 3: Use BimlScript directives to control how BimlScripts are processed, and apply them in package generation.
• Introduction to Biml for SSIS: Build a Package: Build a single SSIS package from Biml using a connection, a control flow task, a data flow with a derived column, and an OLE DB destination.
• Why Biml: An Introduction: What Biml and BimlScript are, why they exist, the tools that author them, the wider product ecosystem, and where Biml fits beyond SSIS.

BimlScript Language

Syntax, code nuggets, directives, comments, variables, annotations, extension methods, and the building blocks of any BimlScript file.

• Basic BimlScript Structure: The basic elements of a BimlScript file are covered.
• Biml Annotations and ObjectTags: Attach extra metadata to Biml objects with annotations and object tags, then read that metadata in higher tier files.
• Biml Syntax Basics and Rules: Learn the structural rules of a Biml file, including root elements, plural collections, naming conventions, illegal characters, and whitespace handling.
• BimlScript Code Nuggets: Survey the five kinds of code nugget that mix C# or VB into a Biml document and look at how text nuggets substitute computed values into the output.
• BimlScript Control Nuggets: Use control nuggets to add loops, conditionals, and external metadata calls that determine which Biml fragments are emitted.
• Building Your Own Extension Method for Biml: Add a custom C# extension method to the Biml object model so a BimlScript template can convert one node type into another with a single call.
• C# and VB Code Files in Biml: Move shared C# or VB logic out of Biml files and into external code files using the code directive so the same helpers can drive many projects.
• C# Extension Methods for Biml: Convert helper methods in a Biml C# code file into extension methods so they can be called directly on Biml objects.
• Code Comments in BimlScript: Each of the three types of BimlScript code comments, along with their uses are covered in this walkthrough.
• Global Directive in Biml: Use the global directive to inject shared content like template language settings, headers, namespaces, or includes into every Biml file at once.
• Include Files in Biml: Use the Biml include directive to copy shared markup from one file into another so common variables, tasks, or fragments live in a single place.
• Stairway to Biml Level 5: Language Elements: Tour the building blocks of a Biml file: text blocks, directives, control blocks, and the RootNode that ties multiple files together across compile tiers.
• Using Biml Annotations: Tag generated SSIS packages with Biml annotations and read those tags in later scripts to drive package grouping and master orchestration.
• VB Option Explicit and Strict in Biml: Use the optionexplicit and optionstrict template attributes to write less verbose Visual Basic BimlScript when full type declarations are not needed.

Tooling & Build Automation

BimlExpress, BimlStudio, BIDS Helper, command-line builds, logging, and the editor experience around authoring Biml.

• Automating Builds, Part 1: Installation: Install the tools needed to run automated Biml builds using the hadron.exe command-line compiler or the MSBuild task.
• Biml Functionality in BIDS Helper: Overview of the Biml Package Generator feature in BIDS Helper, what it produces, and why script-driven Biml beats hand-built SSIS for repetitive packages.
• BimlExpress Preview Pane: Use the BimlExpress preview pane to see expanded Biml output without writing it to a file, including SQL output produced through code that uses GetDropAndCreateDdl.
• BimlScript Intellisense with BimlExpress: The intellisense for creating Biml and BimlScript files using BimlExpress is limited to Biml only, and does not extend to C# or VB code nuggets.
• BimlStudio Tutorial: Generating a Simple Package: A simple tutorial about how to generate a package from different servers using BimlStudio.
• Create Custom BimlExpress Keyboard Shortcuts: Bind any BimlExpress command, such as Check Biml for Errors or Generate SSIS Packages, to a Visual Studio keyboard shortcut.
• Fully Automate the Biml Expansion: Use the BimlStudio command-line compiler to expand Biml files into SSIS packages outside the IDE so the build runs unattended on a schedule or in CI.
• Getting Started with BimlExpress: An introduction to BimlExpress, the free Visual Studio add in for authoring Biml in SSIS projects, including installation, menus, and the code editor.
• Overcoming BimlScript Copy and Paste Issues in Visual Studio: Workarounds for Visual Studio treating BimlScript as XML, which breaks copy and paste of C# code into Biml files.
• Reverse Engineering a DTSX into Biml: Use the Package Importer to convert an existing SSIS dtsx file into Biml when reproducing a specific task or pattern by hand is impractical.
• Roundtrips Between BimlStudio and Visual Studio: Roundtrip SSIS packages between SSDT and BimlStudio to take advantage of the extended Biml toolsets.
• Using NLog with BimlExpress for BimlScript Logging: Add file based logging to BimlScript executions inside BimlExpress by referencing the NLog assembly and writing structured messages to a log file.
• Viewing or Saving the Compiled Biml File: Use a higher tier Biml file to capture the intermediate compiled XML produced after BimlScript expansion, making complex Biml files easier to debug.

Connections & Adapters

Connection managers, third-party source adapters (SAP, Oracle, OData), project connections, and configuration of connection-related settings.

• Automate SSIS From SAP to SQL With Biml and Theobald XtractIS: Drive an SSIS staging load from SAP using a metadata repository, the Theobald XtractIS source, and BimlScript that reads the SAP DD03L data dictionary.
• Biml for OData Source and Connection Manager: Script the SSIS OData source and OData connection manager in Biml using CustomSsisConnection and CustomComponent elements.
• Extract Data from Sage Sotamas 90 into SQL Server with Biml: Move Sage Sotamas 90 ProvideX data into SQL Server by generating destination tables from the vendor XSD, then producing one SSIS package per table with Biml.
• Intro to SSIS From SAP to SQL With Biml and Theobald: Use the TheobaldXtractSapConnection and TheobaldXtractSapSource elements to copy a single SAP table into SQL Server with one Biml file.
• Package Configurations and Connection Managers in Biml: Build SSIS package configurations, connection managers, and parent package variables in Biml when working with the package deployment model.
• Stable Project-Level Connection Manager IDs in Biml: Pin GUIDs on project-level connection managers so regenerated packages keep their bindings to the connection managers they were generated against.
• Using the Attunity Oracle Connector with Biml: An end-to-end process for setting up Oracle XE and accessing data from SSIS using the Attunity Oracle Connection with Biml.

Table & Schema Generation

CREATE TABLE generation, schema metadata extraction, DDL output, and table-level housekeeping driven by Biml.

• Cleaning a Database with Biml: Generate an SSIS package that drops every table in a target database by looping over INFORMATION_SCHEMA until no tables remain.
• Creating a Staging Area with Biml: Generate CREATE TABLE statements for an entire staging environment by reading INFORMATION_SCHEMA from the source database and emitting an ExecuteSQL task per table.
• Creating Tables using Biml and BimlScript: Define tables in Biml, then use BimlScript to generate an SSIS package that runs the CREATE TABLE statements against the target database.
• Generating Data Profiling Tasks for Every Table with BimlScript: Use BimlScript and a small C# loop to emit one Data Profiling Task per table in a source database, producing per-table profile XML files in a single SSIS package.
• Generating SELECT Statements Using GetColumnList: Build SELECT statements from source metadata using the Biml GetColumnList method, including aliases, predicates, and delimiter overrides.
• Housekeeping with Biml: Tag every Biml managed table with an extended property and let the generation template emit a drop step for any tagged table no longer in the metadata.
• Import Biml Metadata with GetQuerySchema: Use GetQuerySchema to import column metadata from a SELECT query, view, table valued function, or stored procedure.
• Import Metadata with GetDatabaseSchema: Use GetDatabaseSchema to pull schema, table, and view metadata from a source database into Biml so generated packages and SQL stay in step with the source.
• Index Management with Biml: Preserve existing indexes on staging tables when generating drop and create DDL by importing the live target indexes back into the Biml table definition.
• Simple Extract Script with ISNULL Conversion: An example showing how to import all the database tables and convert the nulls to defaults.
• Skip Existing Objects with Biml: Compare the Biml definition of a table against the live database before recreating it so unchanged objects keep their data and indexes.
• Using Biml to Output SQL Files: Use BimlScript to write one .sql file per table in RootNode.Tables containing the DROP and CREATE DDL for that table.

Flat Files & Excel

Flat-file imports and exports, Excel worksheets, raw files, and ODBC-over-text source patterns.

• Automating the Staged Delimited Flat File Pattern: Use BimlScript to generate one staging package per delimited flat file by reading file metadata from a SQL table.
• Export Data to Flat Files with Biml: Use BimlScript to loop over every table in a source database, generate matching flat file formats and connections, and build a single package that exports each table to a CSV file in parallel.
• Export to Split Flat Files with Biml: Generate SSIS packages that split a large source table into multiple flat files using OFFSET FETCH and a Biml script that calls another Biml script.
• Extracting a Database Table to a Text File: Generate an SSIS package in Biml that copies a SQL Server table to a delimited flat file using OleDbConnection, FlatFileFormat, and FlatFileDestination.
• Extracting All Tables to Raw Files: Generate a Biml file that extracts every base table in a SQL Server database to its own raw file by querying INFORMATION_SCHEMA with FOR XML PATH.
• Extracting Database Tables to Raw Files: Use a Biml RawFileFormat plus a RawFileDestination paired with a FileConnection to extract a SQL Server table to an SSIS raw file.
• Flat File Delimiters and Text Qualifiers in Biml: Three ways to specify delimiters and text qualifiers on flat file definitions in Biml: the named enum, hex codes, and the literal character with proper encoding or escaping.
• Importing a Text File with Biml: Define the FlatFileConnection, FlatFileFormat, and FlatFileSource needed to read a delimited text file in an SSIS data flow generated from Biml.
• Importing Data From Excel With Biml: Call GetDatabaseSchema on an ExcelConnection to materialize each worksheet as a Biml table and generate the matching staging DDL.
• Iterating Through Excel Worksheets with Biml: Use BimlScript to read worksheet names from an Excel workbook and emit one SSIS data flow per worksheet, all loading into the same target table.
• Loading Flat Files with XML Format Definitions: Generate Biml flat file formats, connections, and matching staging tables from bcp generated XML format files using a small set of extension methods.
• Schema Definitions from an ODBC Text Source: Use an ODBC text driver to expose a folder of flat files as tables, then read schema metadata to drive ETL generation.

Data Flow & Transformations

Dataflow components, lookups, conditional splits, OLEDB Command, ODBC Destination, derived columns, and error routing inside the data flow.

• Automating Lookups with Biml: Drive a Biml package generator from header and detail metadata tables that describe a base SELECT and a sequence of Lookup transforms for dimensional denormalization.
• Biml Configuration for the Lookup Transform: Configure the Lookup transformation in Biml across three patterns: bare bones auto match, explicit column mapping, and routing unmatched rows to a separate output.
• Conditional Splits in the Data Flow: Configure a Conditional Split transformation in Biml to route rows to multiple outputs based on expressions, with special handling for XML reserved characters.
• Defining the Data Flow in Biml: Use default outputs and explicit InputPath elements to control how data flows from one component to the next in a Biml-defined SSIS dataflow.
• Managing Data Destination Insert Errors: Redirect failed inserts on an ADO.NET destination to a separate error table using Biml error handling configuration.
• Metadata-Driven Data Flow Transformations With Biml: Generate Derived Column and Lookup transformations inside an SSIS data flow from rows in a transformation metadata model.
• OLEDB Command Transformation in Biml: Configure the OLEDB Command data flow transformation in Biml to execute a parameterized SQL statement for each input row.
• OLEDB Command with Return Variables in Biml: Call a stored procedure with output parameters from an OLEDB Command transformation and append the return values onto the data flow buffer.
• The ODBC Destination in Biml: Define ODBC destinations in Biml using auto mapping, explicit column mapping, and per-column overrides.
• Using a Merge Task: An explanation and example of the Merge task.

Control Flow & Packages

Variables, parameters, precedence constraints, package execution, expressions, and master-child orchestration at the control-flow level.

• Biml Version of SSIS Expressions: Biml version of common SSIS expressions covering strings, dates, and times.
• Create SSIS Project Parameters from Biml: Generate the Project.params file for an SSIS Project Deployment model project from Biml by writing the parameter XML directly to disk during build.
• Creating SSIS Packages from a Stored Procedure via Biml: A fast technique to build a simple Truncate and Load SSIS package by leveraging a SQL stored procedure and Biml.
• Defining and Using Variables in Biml: Define SSIS variables in Biml at different scopes and reference them from tasks and parameters within their containing scope.
• Executing Stored Procedures and Return Parameters in Biml: Define an Execute SQL Task in Biml that calls a stored procedure and captures the procedure's RETURN value into a package variable.
• For Each Loops with Biml over Flat Files: Generate an SSIS package that loops over every CSV file in a directory, imports each into a single SQL Server target table, and stores the file name with each row.
• Looping Over Twitter Search Terms with ForEachAdoLoop: Extend the BigData Twitter snippet so a single Biml package iterates over a configurable list of search terms.
• Master Child Package Execution in Biml: Generate a master package and a child package with Biml that share an audit run id and a child execution id through parameter bindings.
• Precedence Constraints in Biml: Control SSIS task execution order in Biml using package ConstraintMode and explicit PrecedenceConstraints with logical operators and evaluation values.
• Referencing the Same Custom Script Task in SSIS with Biml: Avoid runtime errors when a Biml package uses the same custom script task more than once by giving each instance a unique ProjectCoreName.
• SSIS Project Parameters from Biml: Why BimlExpress and BI Developer Extensions do not generate SSIS project parameters out of the box, and the file safety reasons behind that decision.
• The Foreach ADO Iterator in Biml: Use the ForEachAdoLoop control-flow task to iterate over a recordset variable and run multiple SQL tasks per row, with locally scoped loop variables.

Metadata-Driven Generation

Generators, frameworks, and patterns where BimlScript loops over a metadata model to emit large numbers of packages.

• Adding Connection Managers to a Metadata Driven Package Generator: Drive root level OleDb connections and per package connection references from a metadata table so generated SSIS packages all share the same connection managers.
• Biml Metadata Starter Kit: A starter kit for creating BimlScript Metadata Driven packages using a Master Data Services back end to store the metadata.
• Building a Simple Package Generator with Biml: Drive SSIS package generation from a small metadata schema covering packages, connections, and connection types, then loop over the metadata to emit one package per row.
• Content Driven Extraction with Biml, Part 1: Define ETL as relational metadata, then generate connection Biml from a stored procedure that returns connection name and connection string rows.
• Content Driven Extraction with Biml, Part 2: Extend a metadata-driven extraction package by emitting per-row containers with truncate, dataflow, and explicit source-to-target column mappings.
• Copy Data Dynamically with BimlScript: Generate one SSIS package per source table with BimlScript, then extend the script to apply per-table WHERE clauses from a metadata table.
• Create and Load a Staging Environment from Scratch in an Hour with Biml: Code samples from the PASS BI Virtual Chapter session showing how to build and load a complete staging environment with Biml in under an hour.
• Deriving a Metadata Model From SQL Server With BimlExpress: Use ImportDB to materialize a SQL Server metadata schema as a DataSet inside BimlExpress, then drive connections, tables, and load packages from it.
• Deriving a Metadata Model From SQL Server With BimlStudio: Read a SQL Server meta schema with ImportDB, then emit a matching MetadataModel and MetadataInstance using the Biml Metadata feature in BimlStudio.
• Driving SSIS Generation From Custom Metadata Tables: Use a custom metadata table to control which packages BimlScript generates and which optional transformations they include.
• Generating Many SSIS Packages With BimlScript: Use BimlScript to read a list of source tables from system metadata and emit one extract and load package per table from a single template.
• Generating SQL Using Biml: Use BimlScript to generate T-SQL scripts from source metadata, including SELECT statements and CREATE VIEW definitions across hundreds of tables.
• Loading Tables by Foreign Key Topology with Biml: Linear Loads: Use a topology sort extension method to walk foreign key dependencies and generate SSIS packages that recreate and load tables in the right order.
• Loading Tables by Foreign Key Topology with Biml: Parallel Loads: Extend the topology aware load pattern so independent branches run in parallel while still respecting foreign key dependencies.
• Meta Data Driven SSIS Solution with Biml: Architecture overview for a metadata driven SSIS solution that uses Biml and BimlScript to generate table load packages and a master package from a source to target mapping table.
• Metadata Based SSIS with Biml, Part 1: Drive Biml table generation from a small metadata table in the target database, attach the source SELECT as an annotation, and emit the create plus load packages.
• Metadata Based SSIS with Biml, Part 2: Extend the metadata layer with multiple source connections and per table column lists, then carry the source connection and column choice through annotations into the load package.
• Metadata Design Patterns in Biml: Build SSIS staging packages from a flat file using Biml, then convert hardcoded values into a metadata driven generator that reads file format and column details from a database.
• A Minimalistic Approach to Processing Packages with Biml: Drive SSIS processing packages, including stored procedure execution and SSAS database processing, from a tiny metadata model that scales to many tasks.
• Reverse Engineering Metadata with Biml: Use BimlScript and ImportDB to reverse engineer table metadata out of an existing staging database into a metadata model that drives future Biml generation.
• Using Metadata and Biml to Control Deployment: Drive multi-region SSIS deployment from a small set of metadata tables so the same source loads the same tables into per-region target databases.

Reuse, Transformers & Templates

Modularization through CallBimlScript, tiered files, BimlStudio Transformers, bulk template changes, and custom validators.

• Asset Declaration and Scripting Basics in Biml, Part 1: Introduce BimlScript as a way to iterate over a package template and discuss why splitting a project across tiered Biml files makes sense.
• Asset Declaration and Scripting Basics in Biml, Part 2: Define BI assets such as tables in one Biml file, then iterate them in a second tiered file to generate truncate-and-load packages from RootNode metadata.
• BimlScript Transformers Primer: An introduction to transformers in BimlScript, a feature available in BimlStudio.
• BimlStudio Transformers Webinar Content: A webinar demonstrating BimlStudio Transformers, the "secret sauce" of Biml.
• Bulk Changes to Tasks in BimlExpress: Use AllDefinedSuccessors at a high tier to apply a default property such as DefaultBufferMaxRows to every Dataflow Task in a project.
• Bulk Changes With XML Templates in Biml: Use template Biml objects and a high tier merge file to push template columns and template events onto every other table or package in BimlExpress.
• CallBimlScript for Reuse: Reuse Biml across files and projects by moving common patterns into a callee file and invoking it with parameters from a caller file.
• Introduction to CallBimlScriptWithOutput: Use CallBimlScriptWithOutput to return both Biml code and a dynamic object from a callee, so the caller can act on values produced inside the included script.
• Making Biml Files Less Complex: Reduce mixed XML and C# complexity in Biml files by splitting into includes, using CallBimlScript, and pushing string assembly into T-SQL stored procedures.
• Modular Table Replication with CallBimlScript: Split a copy-all-tables Biml solution into a driver file and a parameterized package callee so the per-table logic lives in one place.
• PrecedenceConstraints using Transformers: A follow up on the power of using Transformers in BimlStudio.
• Tiered Biml Files: Split a Biml solution across tiered files so that connections, packages, and orchestrators can be built in dependency order from a shared RootNode.
• Transformer LocalReplace Before: A transformer that inserts transformations before a component.
• Writing Custom Validators Using Biml: Create custom Biml errors, warnings, and messages by hooking into the validation system.

Dimensions, Data Vault & CDC

Slowly changing dimensions, Data Vault patterns, and Change Data Capture pipelines built with Biml.

• Auto Generate Data Vault using Biml, Part 1: Webinar Content: Webinar walkthrough showing how Biml fully generates a Data Vault data warehouse from source metadata.
• Biml for a Type 1 Slowly Changing Dimension: Build a reusable BimlScript template that loads a Type 1 slowly changing dimension with hash key change detection and set based updates.
• Change Data Capture and Biml: Build SSIS Change Data Capture initial load and incremental load packages in Biml using the SQL Server CDC components as custom tasks.
• Data Vault Metadata Modelling with Biml: Extend an auto-generated Data Vault to handle business keys, satellite splits by fast-changing attributes, and per-attribute manual overrides driven from metadata.
• Hybrid Type 2 Slowly Changing Dimension with Biml: Build a reusable BimlScript template that loads a hybrid Type 2 (Type 6) dimension with both historical and current attributes.
• Type 2 Slowly Changing Dimension with Biml: Build a reusable BimlScript template that loads a Type 2 slowly changing dimension using set based updates and hash key change detection.

Analysis Services

Multidimensional SSAS cubes, dimension processing, and partition processing with Biml.

• Building a Multidimensional SSAS Database with Biml: Define dimension and measure group metadata directly on the underlying tables, then assemble a multidimensional cube with role playing dimensions and partitions.
• SSAS Dimension Processing with Biml: Build SSIS packages that ProcessUpdate every dimension in an SSAS database, either as a single processing task or as one task per dimension inside a parallel container.
• SSAS Partition Processing with Biml: Use AMO inside BimlScript to find the most recently processed partitions in each SSAS measure group and emit a Partition Processing task that targets only those partitions.
• SSAS Processing Patterns with Biml: Generate SSIS packages that process every dimension, the most recent fact partitions, and cube indexes in an SSAS database from a single Biml script.

Azure & Cloud

Azure Data Factory, Azure Data Lake Storage, and other cloud-targeted Biml generation patterns.

• Automating Azure Data Factory v2 With Biml: Use the DataFactory, LinkedServices, Datasets, Pipelines, and Triggers tags in BimlStudio to generate Azure Data Factory v2 JSON from source metadata.
• Generating Azure Data Factory Datasets and Pipelines With Biml: Use BimlScript to generate Azure Data Factory v1 input and output dataset JSON files for hundreds of source tables driven from a metadata spreadsheet.
• Moving ETL Loads to Azure, Part 1: Webinar Content: Webinar walkthrough demonstrating the pattern for migrating SSIS ETL workloads to Azure with live BimlScript.

Performance & Scale

Performance tuning, parallel load balancing, and throughput patterns for Biml-generated SSIS workloads.

• Delayed Durability for High Throughput Biml Migrations: When parallel SSIS packages from a Biml framework saturate the log writer with WRITELOG waits, enabling delayed durability on staging databases can cut total runtime in half.
• Optimizing and Balancing Unit of Work in SSIS With T-SQL and Biml: Use a T-SQL bin packing routine and a BimlScript template to assign tables to balanced parallel containers and generate the matching SSIS package.
• SSIS Fast Load and the Silent Truncation Trap: Why the OLE DB Destination in fast load mode silently truncates oversized values, what row by row mode does instead, and how to plan for it in a Biml driven project.
• SSIS Parallel Processing with LINQ in Biml: Use LINQ OrderBy, Skip, and Take inside BimlScript to chunk a large set of source tables into evenly sized groups, each loaded by its own SSIS package.