Skip to main content

Creating Tables using Biml and BimlScript

Why This Pattern Matters​

When building SSIS samples, the source and destination tables often need to exist before any package will run. Defining those tables in Biml keeps the schema in source control alongside the packages, and a small BimlScript file can turn the table definitions into an Execute SQL Task that creates them in the database. The same pattern scales to dozens or hundreds of tables.

Defining Tables in Biml​

The example below defines two tables, 'OrderHeader' and 'OrderLine', with a primary key on each and a foreign key from 'OrderLine' back to 'OrderHeader'.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <OleDbConnection Name="DemoConn" ConnectionString="Server=.;Initial Catalog=DemoDB;Provider=SQLNCLI11.1;Integrated Security=SSPI;" />
  </Connections>
  <Tables>
    <Table Name="OrderHeader" ConnectionName="DemoConn">
      <Columns>
        <Column Name="OrderId" DataType="Int32" IdentityIncrement="1" IdentitySeed="1" />
        <Column Name="OrderDate" DataType="DateTime" />
        <Column Name="CustomerName" DataType="String" Length="50" />
      </Columns>
      <Keys>
        <PrimaryKey Name="OrderHeaderPK">
          <Columns>
            <Column ColumnName="OrderId" />
          </Columns>
        </PrimaryKey>
      </Keys>
    </Table>
    <Table Name="OrderLine" ConnectionName="DemoConn">
      <Columns>
        <Column Name="OrderLineId" DataType="Int32" IdentityIncrement="1" IdentitySeed="1" />
        <TableReference Name="OrderId" TableName="OrderHeader" />
        <Column Name="ProductName" DataType="String" Length="50" />
        <Column Name="Qty" DataType="Int16" />
        <Column Name="UnitPrice" DataType="Currency" />
      </Columns>
      <Keys>
        <PrimaryKey Name="OrderLinePK">
          <Columns>
            <Column ColumnName="OrderLineId" />
          </Columns>
        </PrimaryKey>
      </Keys>
    </Table>
  </Tables>
</Biml>

Tables go inside a 'Table' element, which contains 'Columns' and 'Keys'. Many attributes have defaults: 'OrderId' has no 'DataType' attribute and so defaults to 'Int32'. Foreign key relationships use a 'TableReference' column pointing at another table in the project. Multi-column relationships use 'MultipleColumnTableReference'.

Generating CREATE Statements with BimlScript​

A BimlScript template iterates over the project's tables and produces an Execute SQL Task for each one, embedding the SQL needed to create the table. Place this code in a separate file (for example, 'CreateTables.biml') so the script and the table definitions live in different files.

<#@ template language="C#" hostspecific="True" #>

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Packages>
    <Package Name="Create Tables" AutoCreateConfigurationsType="None" ConstraintMode="Linear">
      <Tasks>
        <# foreach(var table in RootNode.Tables) { #>
        <ExecuteSQL Name="Create <#=table.Name#>" ConnectionName="<#=table.Connection.Name#>">
          <DirectInput>
            <#=table.GetTableSql()#>
          </DirectInput>
        </ExecuteSQL>
        <# } #>
        <# foreach(var table in RootNode.Dimensions) { #>
        <ExecuteSQL Name="Create <#=table.Name#>" ConnectionName="<#=table.Connection.Name#>">
          <DirectInput>
            <#=table.GetTableSql()#>
          </DirectInput>
        </ExecuteSQL>
        <# } #>
        <# foreach(var table in RootNode.Facts) { #>
        <ExecuteSQL Name="Create <#=table.Name#>" ConnectionName="<#=table.Connection.Name#>">
          <DirectInput>
            <#=table.GetTableSql()#>
          </DirectInput>
        </ExecuteSQL>
        <# } #>
      </Tasks>
    </Package>
  </Packages>
</Biml>

The template language directive at the top tells the compiler the embedded code is C#. Inside the 'Tasks' element, the script loops over 'RootNode.Tables' (and the related 'Dimensions' and 'Facts' collections, which are special table types). For each one, it emits an 'ExecuteSQL' task whose 'DirectInput' is the result of 'GetTableSql()' on the table node.

Producing the Package​

BimlScript cannot operate on objects defined in the same file, which is why the table definitions live in 'TableDefinitions.biml' and the script lives in 'CreateTables.biml'. Multi-select both files in Solution Explorer, then choose Expand Biml File. The compiler reads both files together and emits a single 'Create Tables.dtsx' package containing one Execute SQL Task per table.

What the Generated SQL Looks Like​

Each Execute SQL Task contains the create script for its table. For 'OrderHeader' the generated T-SQL looks like this:

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

-------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[OrderHeader]') AND type IN (N'U'))
DROP TABLE [OrderHeader]
GO

CREATE TABLE [OrderHeader]
(
   [OrderId] int IDENTITY(1,1) NOT NULL
 , [OrderDate] datetime NOT NULL
 , [CustomerName] nvarchar(50) NOT NULL
 , CONSTRAINT [OrderHeaderPK] PRIMARY KEY CLUSTERED
   (
     [OrderId] ASC
   ) WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
)
ON [PRIMARY]
WITH (DATA_COMPRESSION = NONE)
GO
-------------------------------------------------------------------

Ordering and Dependencies​

The Execute SQL Tasks land in the package in the same order the tables appear in the Biml file. If a table has dependencies on another table (for example, a foreign key), make sure the parent table is listed first so the create statements run in the right order.

What to Try Next​

Once table generation works, the same metadata can drive related tasks: copying data between source and destination, generating views, or producing schema-diff scripts. Each one uses the same pattern: iterate over the table nodes and emit the right SSIS or T-SQL output.