Skip to main content

Biml Syntax Basics and Rules

Why These Rules Matter​

Biml is a human readable XML language that compiles into SQL Server objects such as tables, views, SSIS packages, and SSAS cubes. Knowing the structural rules of a Biml file before writing one prevents the most common compile errors: mis-cased element names, missing closing tags, illegal characters in inline T-SQL, and stray whitespace inside attribute values.

This walkthrough covers the building blocks of a valid Biml file using a small Environment file as the running example.

A Minimal Environment File​

A common starting point is an Environment.biml file that holds the connections, databases, and schemas used everywhere else in the project. Grouping these objects in one file keeps later BimlScript files small and focused.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <OleDbConnection Name="connDW" ConnectionString="Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=AnalyticsDW;Data Source=BuildServer" />
    <OleDbConnection Name="connSource" ConnectionString="Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=OpsApp;Data Source=BuildServer" />
  </Connections>
  <Databases>
    <Database Name="AnalyticsDW" ConnectionName="connDW" />
    <Database Name="OpsAppSource" ConnectionName="connSource" />
  </Databases>
  <Schemas>
    <Schema Name="dbo" DatabaseName="OpsAppSource" />
    <Schema Name="dbo" DatabaseName="AnalyticsDW" />
    <Schema Name="staging" DatabaseName="AnalyticsDW" />
    <Schema Name="warehouse" DatabaseName="AnalyticsDW" />
  </Schemas>
</Biml>

Root Element​

Every Biml file begins with a root 'Biml' element that references the XSD describing the Biml language. The XSD defines the rules for every Biml element and attribute. Inside the root element are plural collection elements, such as 'Connections', 'Databases', 'Schemas', and 'Packages'. Each plural collection contains one or more individual objects, and every plural collection and nested object must have a closing tag.

Object Attributes​

Each Biml object exposes its own set of attributes, only some of which are required. In the example above, two databases are defined, each with a connection, and several schemas are bound to one of those databases.

All Biml elements and attributes use Pascal Case ('OleDbConnection', 'ConnectionString'), and all identifiers and attribute values are case sensitive. A schema named 'Warehouse' is not the same as one named 'warehouse'.

Comments​

Biml uses standard XML comment syntax, and comments may span multiple lines.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <!-- This file holds shared
       connection and database
       definitions for the project. -->
</Biml>

Object Naming and Scope​

Schema names cannot be globally unique because every database typically has a 'dbo' schema. Names only need to be unique within their parent scope. Schema names must be unique within their parent 'Database', table names must be unique within their parent 'Schema', and so on.

Illegal XML Characters and CDATA​

Biml is XML, so a handful of characters have reserved meaning. Comparison characters in T-SQL or SSIS expressions are the most common offenders. For example, an Execute SQL Task with the following Direct Input fails to parse:

SELECT FieldA FROM SomeTable WHERE FieldA < 5

One option is to replace each reserved character with its XML entity reference, for example replacing the less-than sign with the entity for less-than. That works but makes the SQL harder to read.

A cleaner option is to wrap the SQL in a 'CDATA' section, which tells the XML parser not to process the content. CDATA sections may span multiple lines.

<![CDATA[ SELECT FieldA FROM SomeTable WHERE FieldA < 5 ]]>

Whitespace​

The compiler ignores whitespace inside element markup, so extra spaces, tabs, or carriage returns between attributes and elements have no effect.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Databases>
    <Database Name="AnalyticsDW"  ConnectionName="connDW" />
  </Databases>
</Biml>

Whitespace inside attribute values is significant. The following file fails because the trailing spaces in 'ConnectionName' make the value not match any defined connection:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Databases>
    <Database Name="AnalyticsDW" ConnectionName="connDW   " />
  </Databases>
</Biml>

The compiler reports a reference resolution error because the trailing spaces change the value being looked up.

Summary​

A valid Biml file starts with the 'Biml' root element, groups objects into plural collection elements, uses Pascal Case names with strict case sensitivity, scopes uniqueness to the parent element, escapes or wraps reserved XML characters in 'CDATA' sections, and treats whitespace inside attribute values as significant. Following these rules avoids most parser-level errors before any SSIS, SSAS, or T-SQL output is produced.