Skip to main content

Using Biml to Output SQL Files

Why Emit SQL from Biml​

Most Biml examples generate SSIS packages from a template. The same engine can emit SQL Server DDL just as easily. When the goal is to ship a folder of CREATE scripts (for source control, migrations, or handoff to a DBA), File.WriteAllText inside a Biml code nugget produces one file per table without needing a separate templating tool.

This walkthrough assumes the project already has tables defined in RootNode.Tables.

The Full Script​

<#@ tier="9000" #>
<#@ import namespace="System.IO" #>
<# string root = @"C:\Output\"; #>
<# foreach (var tableNode in RootNode.Tables) { #>
  <#
    File.WriteAllText
    (
      root + tableNode.SchemaQualifiedName + ".sql",
      tableNode.GetDropAndCreateDdl(),
      Encoding.UTF8
    );
  #>
<# } #>

Tier and Imports​

The tier directive ensures the file runs after every other Biml in the project, so RootNode.Tables is fully populated before the loop runs. The import directive brings System.IO and System.Text into scope so File.WriteAllText and Encoding.UTF8 resolve without fully qualified names:

<#@ tier="9000" #>
<#@ import namespace="System.IO" #>
<#@ import namespace="System.Text" #>

The Output Path​

A simple string variable holds the directory where the .sql files will be written. Using the verbatim string syntax keeps the trailing backslash readable on Windows paths:

<# string root = @"C:\Output\"; #>

Looping and Writing​

The loop iterates every table in the root node. Inside the code nugget, File.WriteAllText takes three arguments:

  • The output filename. Concatenating the root path, the schema qualified name of the table, and the .sql extension gives one unique file per table.
  • The file content. GetDropAndCreateDdl on an AstTableNode returns a CREATE statement (and optionally a DROP) for that table. Replacing it with GetSelectSql produces SELECT statements instead.
  • The text encoding. UTF8 is explicit here. Omitting the third argument also defaults to UTF-8 without a byte order mark.
<# foreach (var tableNode in RootNode.Tables) { #>
  <#
    File.WriteAllText
    (
      root + tableNode.SchemaQualifiedName + ".sql",
      tableNode.GetDropAndCreateDdl(),
      Encoding.UTF8
    );
  #>
<# } #>

The result is a folder of .sql files, one per table, each containing the DDL needed to recreate it. The same pattern works for any string returning method on the AST: SELECT statements, INSERT statements, MERGE statements, or anything else that can be derived from table metadata.