Use Biml and output .SQL files

If you are developing BIML either in BimlExpress or BimlStudio you have the capabilities to output SSIS packages from a defined template. The templates for SSIS can be created manually with BIML or by using C# to extend BIML and script out your templates.

Examples shown often demonstrate how to automatically generate SSIS packages.
But what about SQL Server DDL defintions?

Lets dig into this example code where I show how easy it is to output SQL Server DDL defintions with BIML.
This example assumes you already have collections defined inside RootNode.Tables.

Now let’s start by breaking down the code.

First part consists of importing the required namespaces for this operation.

We create a string containg the root path for where we’d like to output the result.

Onto the cool bits. Here we loop over all items existing inside RootNode.Tables. We use a C# code nuggets to call File.WriteAllText and pass in three options.

  1. The filename we’d like our output to have.
  2. The content we’d like our output to have.
  3. The encoding we’d like our output to have.
    This is optional since the methods default encoding is UTF-8 encoding without a Byte-Order Mark (BOM).

And with this simple code example we’ve created one .sql file containing a DDL statement for each table defined in RootNode.Tables.

Leave a Reply

Your email address will not be published. Required fields are marked *