Making Biml Files Less Complex

Why File Complexity Grows

A Biml file mixes XML structure with embedded C# code. As the script grows, the two layers tangle and the file becomes hard to read. Web frameworks address this with code-behind. Biml does not have that option, but it does offer two practical alternatives:

1. Split content across files using includes and callable BimlScript.
2. Move string assembly into T-SQL so the Biml stays close to plain XML.

Splitting Across Files

The 'include' directive inserts a fragment of static XML from another file into a Biml file. For dynamic content with parameters, the 'CallBimlScript' function calls another Biml file as a function and passes property values into it. The callee file declares the property contract and the caller passes values when invoking it.

Pushing String Assembly Into T-SQL

A typical Biml solution combines Biml files with metadata that describes the packages to build. A common metadata layout uses a table mapping table and a column mapping table in SQL Server. A stored procedure can join the two and return one row per package with all the strings the Biml needs.

The examples below assume a 'meta.ColumnMapping' source-to-target mapping table.

Building a Quoted Source Column List With FOR XML PATH

The 'FOR XML PATH' construct collapses rows into a single string. The first attempt produces a string with a leading delimiter:

SELECT srcColumnList = (
  SELECT ', ' + srcColumn
  FROM meta.ColumnMapping
  WHERE srcColumn IS NOT NULL
  FOR XML PATH('')
);

Wrapping the expression in 'STUFF' strips the leading comma and 'QUOTENAME' adds bracket delimiters around each name:

SELECT srcColumnList = STUFF((
  SELECT ', ' + QUOTENAME(srcColumn)
  FROM meta.ColumnMapping
  WHERE srcColumn IS NOT NULL
  FOR XML PATH('')
), 1, 2, '');

The result is a SELECT-ready column list such as 'PartyId, GivenName, Surname, Gender'.

Building a Data Conversion Column List

The same pattern can produce the inner XML fragment for a Biml 'DataConversion' transformation. The target Biml looks like this:

<DataConversion Name="DC">
  <Columns>
    <Column SourceColumn="GivenName" TargetColumn="FirstName"
            DataType="AnsiString" Length="40" CodePage="1252" />
    <!-- more columns -->
  </Columns>
</DataConversion>

The T-SQL that produces the inner Column elements wraps the assembly in two 'REPLACE' calls so the angle brackets that 'FOR XML' escapes get restored:

SELECT DCColumns = REPLACE(REPLACE(STUFF((
  SELECT char(10)
       + '<Column SourceColumn="' + srcColumn
       + '" TargetColumn="' + tgtColumn
       + '" DataType="' + tgtDataType
       + CASE WHEN tgtDataType = 'AnsiString'
              THEN '" Length="' + CAST(tgtLength AS varchar(10)) + '" CodePage="1252" />'
              ELSE '" />'
         END
  FROM meta.ColumnMapping
  FOR XML PATH('')
), 1, 1, ''), '&lt;', '<'), '&gt;', '>');

The 'char(10)' line break is for readability of the result; the Biml compiler does not need it. The two 'REPLACE' calls undo the escape that 'FOR XML' applies to angle brackets.

A Stored Procedure That Returns Everything Per Package

A single procedure can package up all the strings a row driven Biml file needs:

CREATE PROCEDURE meta.getPackageDetails (@MappingType varchar(50)) AS
SELECT
    PackageName       = t.MappingName,
    TargetTable       = t.tgtSchema + '.' + t.tgtTable,
    TargetConnection  = t.tgtConnection,
    SourceTable       = t.srcSchema + '.' + t.srcTable,
    SourceConnection  = t.srcConnection,
    srcColumnList     = STUFF((
      SELECT ', ' + QUOTENAME(srcColumn)
      FROM meta.ColumnMapping
      WHERE srcColumn IS NOT NULL
        AND TableMappingID = t.TableMappingID
      FOR XML PATH('')
    ), 1, 2, ''),
    DCColumns         = REPLACE(REPLACE(STUFF((
      SELECT char(10)
           + '<Column SourceColumn="' + srcColumn
           + '" TargetColumn="' + tgtColumn
           + '" DataType="' + tgtDataType
           + CASE WHEN tgtDataType = 'AnsiString'
                  THEN '" Length="' + CAST(tgtLength AS varchar(10)) + '" CodePage="1252" />'
                  ELSE '" />'
             END
      FROM meta.ColumnMapping
      WHERE TableMappingID = t.TableMappingID
      FOR XML PATH('')
    ), 1, 1, ''), '&lt;', '<'), '&gt;', '>')
FROM meta.TableMapping t
WHERE t.MappingType = @MappingType;

The Resulting Biml File

With the heavy string work in T-SQL, the Biml file shrinks to a row loop that pastes each prebuilt string into the right place:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <#@ include file="Connection.biml" #>
  <Packages>
    <#
      string sConn = "Provider=SQLNCLI11;Server=.\\SQL2019;Initial Catalog=BimlSamples;Integrated Security=SSPI;";
      string sSQL  = string.Format("Exec meta.getPackageDetails {0}", "dim");
      DataTable tblPackages = ExternalDataAccess.GetDataTable(sConn, sSQL);
      foreach (DataRow pkg in tblPackages.Rows) {
    #>
      <Package Name="<#=pkg["PackageName"]#>" ConstraintMode="Linear">
        <Tasks>
          <Dataflow Name="DFT <#=pkg["PackageName"]#>">
            <Transformations>
              <OleDbSource Name="ODS Source" ConnectionName="<#=pkg["SourceConnection"]#>">
                <DirectInput>
                  SELECT <#=pkg["srcColumnList"]#>
                  FROM <#=pkg["SourceTable"]#>
                </DirectInput>
              </OleDbSource>

              <DataConversion Name="DC">
                <Columns>
                  <#=pkg["DCColumns"]#>
                </Columns>
              </DataConversion>

              <OleDbDestination Name="ODD Target" ConnectionName="<#=pkg["TargetConnection"]#>">
                <ExternalTableOutput Table="<#=pkg["TargetTable"]#>" />
              </OleDbDestination>
            </Transformations>
          </Dataflow>
        </Tasks>
      </Package>
    <# } #>
  </Packages>
</Biml>
<#@ import namespace="System.Data" #>

Choosing Where to Put the Logic

Move static XML to includes. Move parameterized XML to a callable Biml file. Move repetitive string assembly to T-SQL when the metadata already lives in SQL. The right split depends on how comfortable the team is with C# versus T-SQL; the goal is a Biml file that is mostly XML structure with small, readable substitutions.