Generating SQL Using Biml

Why Generate SQL with Biml?

Biml is best known as a way to generate SSIS packages from metadata, but the same engine can produce any text output, including plain T-SQL. For teams that already use Biml to build SSIS packages, generating SQL the same way keeps automation in a single tool and language. The same metadata that drives package generation can also drive view definitions, table scripts, and statement generators without bringing in another templating engine.

A typical use case: a source database is about to ship a new version with renamed columns, dropped columns, and changed data types. Hundreds of downstream packages and stored procedures depend on the original shape. Instead of rewriting every consumer, a Biml script can generate compatibility views over the new tables that present the original column names and types. The downstream code keeps working, and the script that builds the views is checked in alongside the rest of the metadata.

A Quick Reminder About Biml

Biml has two layers:

1. Flat Biml is plain XML that describes objects to generate, such as SSIS packages.
2. BimlScript is C# or VB embedded inside Biml using code nuggets like '<# ... #>'.

A minimal flat Biml file looks like this:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Packages>
    <Package Name="EmptySSISPackage">
    </Package>
  </Packages>
</Biml>

A trivial BimlScript variable assignment looks like this:

<# var demoVariable = "Hello World!"; #>

The compiler reads the BimlScript, expands it into flat Biml, then turns that flat Biml into the requested output (for example, a .dtsx file).

BimlScript Without Biml

Although the compiler is built to emit Biml objects, the BimlScript engine itself just emits text. Anything written outside of code nuggets goes straight to the output. That means a BimlScript file can produce SQL, PowerShell, JSON, or any other text format simply by leaving the Biml elements out and writing the desired text directly.

For SQL generation, that property is what makes the technique work: the file is a BimlScript template whose output happens to be a SQL script rather than an XML document.

Generating a Simple SELECT for Every Source Table

The 'GetSelectSql()' method on a Biml table node returns a SELECT statement for that table. Combined with 'GetDatabaseSchema()' on a connection, the script iterates over every table in a source connection and emits one SELECT per table:

<#
    var srcConn = SchemaManager.CreateConnectionNode(
        "OpsSource",
        "Data Source=DataServer;Initial Catalog=OperationalDB;Integrated Security=SSPI;Provider=SQLNCLI11;");
    var srcSchema = srcConn.GetDatabaseSchema();
#>

<# foreach (var srcTable in srcSchema.TableNodes) { #>
<#=srcTable.GetSelectSql()#>
<# } #>

The output is a plain text file containing one SELECT statement per table, generated directly from live source metadata.

Generating Compatibility Views from Metadata

The same loop can wrap each SELECT in a CREATE VIEW statement to produce hundreds of views in seconds:

<#
    var srcConn = SchemaManager.CreateConnectionNode(
        "OpsSource",
        @"Data Source=DataServer;Initial Catalog=OperationalDB;Integrated Security=SSPI;Provider=SQLNCLI11;");
    var srcSchema = srcConn.GetDatabaseSchema();
#>

<# foreach (var srcTable in srcSchema.TableNodes) { #>
CREATE VIEW compat.<#=srcTable.Schema.Name#>_<#=srcTable.Name#> AS
<#=srcTable.GetSelectSql()#>
GO
<# } #>

Roughly ten lines of script can create two, fifty, or a thousand view definitions, all named consistently and all driven from the same metadata.

Why Choose Biml Over Dynamic T-SQL or Other Tools

Dynamic T-SQL, PowerShell, Excel macros, and T4 templates can all generate SQL from metadata. The argument for using Biml is consolidation: a team that is already writing BimlScript to generate SSIS packages already has the templates, the connection metadata, and the build process. Adding SQL generation to the same project avoids introducing a second toolchain and lets the same metadata drive both the packages and the supporting SQL.

Next Steps

Once 'GetSelectSql()' is in place, the same pattern extends to JOIN-aware SELECT statements, INSERT statements, MERGE statements for incremental loads, and any other repetitive T-SQL that follows a metadata-driven shape. The technique is the same: iterate over the metadata, emit text in the desired format, and let the BimlScript engine do the substitution.