Using Metadata and Biml to Control Deployment

Why Metadata Driven Deployment

Metadata is usually associated with describing source systems. The same idea works for the deployment side: the metadata can declare which source connections feed which target environments, and a Biml script can stamp out one set of packages per target. This walkthrough builds a small metadata model that drives staging deployment to three regional databases (US, ASIA, EUROPE) sharing a single source. The same approach extends to TEST and PROD environments or any other target dimension.

Create four databases up front: a control database that holds the metadata, plus three regional staging databases that will receive the data.

The Metadata Schema

Three tables and two views describe connections, target environments, and the tables to load. The view layer joins connections to active targets and produces one row per target source pair:

USE [DeployControl]
GO
CREATE TABLE [dbo].[DeployMeta_Connections] (
  [TargetName] [nvarchar](50) NULL,
  [ConnectionName] [nvarchar](50) NULL,
  [ConnectionServer] [nvarchar](50) NULL,
  [ConnectionDatabase] [nvarchar](50) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[DeployMeta_Tables] (
  [ConnectionName] [nvarchar](50) NULL,
  [TableName] [nvarchar](50) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[DeployMeta_Targets] (
  [TargetName] [nvarchar](50) NULL,
  [TargetServer] [nvarchar](50) NULL,
  [TargetDatabase] [nvarchar](50) NULL,
  [IsActive] [bit] NULL
) ON [PRIMARY]
GO
CREATE VIEW [dbo].[vDeployMeta_Connections] AS
  SELECT t.TargetName
    , c.ConnectionName + '_' + t.TargetName AS ConnectionName
    , c.ConnectionName AS BaseConnection
    , c.ConnectionServer
    , c.ConnectionDatabase
    , 'Source' AS Type
  FROM DeployMeta_Connections AS c
  INNER JOIN DeployMeta_Targets AS t ON t.TargetName = c.TargetName
  WHERE ISNULL(t.IsActive, 0) = 1
  UNION ALL
  SELECT TargetName
    , 'Target_' + TargetName AS ConnectionName
    , TargetName AS BaseConnection
    , TargetServer
    , TargetDatabase
    , 'Target' AS Type
  FROM dbo.DeployMeta_Targets
  WHERE ISNULL(IsActive, 0) = 1
GO
CREATE VIEW [dbo].[vDeployMeta_Tables] AS
  SELECT c.ConnectionName, t.TableName
  FROM dbo.vDeployMeta_Connections AS c
  INNER JOIN dbo.DeployMeta_Tables AS t ON c.BaseConnection = t.ConnectionName
GO

Insert one row per region in DeployMeta_Targets, one row per source connection per region in DeployMeta_Connections, and one row per table to load in DeployMeta_Tables. The view layer multiplies a single source connection definition into one connection per active target.

A Reusable Code File

A small VB module centralizes connection string building, a helper that runs SQL and returns a non empty list of strings, and a default ImportOptions value used during schema import:

Imports Varigence.Biml.CoreLowerer.SchemaManagement
Imports Varigence.Biml.Extensions
Imports Varigence.Languages.Biml
Imports Varigence.Languages.Biml.Connection
Imports System.Data
Imports System.Collections.Generic

Public Class DeployHelpers
  Public Shared ControlDb As String = "DeployControl"
  Public Shared ControlServer As String = "Localhost"

  Public Shared Function GetConnectionString(server As String, db As String) As String
    Return "Provider=SQLNCLI11;Server=" & server & ";Initial Catalog=" & db & ";Integrated Security=SSPI;"
  End Function

  Public Shared Function GetNonEmptyList(conn As AstDbConnectionNode, sql As String) As List(Of String)
    Dim tmpList As New List(Of String)
    If sql.Contains(" ") = 0 Then sql = "select * from " + sql
    Dim dt As DataTable = ExternalDataAccess.GetDataTable(conn.ConnectionString, sql)
    For Each dr As DataRow In dt.Rows
      tmpList.Add(dr.Item(0).ToString())
    Next
    If tmpList.Count = 0 Then tmpList.Add("NONEMPTYFILLER")
    Return tmpList
  End Function

  Public Shared Function DefaultImportOptions()
    Return ImportOptions.ExcludeIdentity Or ImportOptions.ExcludePrimaryKey Or ImportOptions.ExcludeUniqueKey Or ImportOptions.ExcludeColumnDefault _
      Or ImportOptions.ExcludeIndex Or ImportOptions.ExcludeCheckConstraint Or ImportOptions.ExcludeForeignKey
  End Function
End Class

The non empty list filler ensures downstream IN clauses always see at least one value so the generated SQL stays valid even when the metadata is incomplete.

Tier 1: Connections

The first tiered file emits the control connection plus one connection per row in vDeployMeta_Connections. Annotations on each connection record what type it is (Source or Target), the base connection name, the database name, and the target name. The Type tag is the key that downstream tiers use to distinguish the two:

<#@ template language="VB" tier="1" #>
<#@ code file="../Code/DeployHelpers.vb" #>
<#@ import namespace="System.Data" #>
<#@ import namespace="DeployHelpers" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <OleDbConnection Name="Control" ConnectionString="<#= GetConnectionString(ControlServer, ControlDb) #>" />
    <# For Each dr As DataRow In ExternalDataAccess.GetDataTable(GetConnectionString(ControlServer, ControlDb), "Select * from vDeployMeta_Connections").Rows #>
      <OleDbConnection Name="<#= dr("ConnectionName") #>" ConnectionString="<#= GetConnectionString(dr("ConnectionServer"), dr("ConnectionDatabase")) #>">
        <Annotations>
          <Annotation AnnotationType="Tag" Tag="Type"><#= dr("Type") #></Annotation>
          <Annotation AnnotationType="Tag" Tag="BaseConnection"><#= dr("BaseConnection") #></Annotation>
          <Annotation AnnotationType="Tag" Tag="DatabaseName"><#= dr("ConnectionDatabase") #></Annotation>
          <Annotation AnnotationType="Tag" Tag="TargetName"><#= dr("TargetName") #></Annotation>
        </Annotations>
      </OleDbConnection>
    <# Next #>
  </Connections>
</Biml>

Tier 2 and 3: Databases and Schemas

The next two tiers project a Database node for each target connection and a default Schema for each target database, carrying the TargetName annotation forward:

<#@ template language="VB" tier="2" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Databases>
    <# For Each target As AstConnectionBaseNode In RootNode.Connections.Where(Function(c) c.GetTag("Type") = "Target") #>
      <Database Name="<#= target.GetTag("DatabaseName") #>" ConnectionName="<#= target.Name #>">
        <Annotations>
          <Annotation AnnotationType="Tag" Tag="TargetName"><#= target.GetTag("TargetName") #></Annotation>
        </Annotations>
      </Database>
    <# Next #>
  </Databases>
</Biml>

<#@ template language="VB" tier="3" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Schemas>
    <# For Each db As AstDatabaseNode In RootNode.Databases #>
      <Schema Name="dbo" DatabaseName="<#= db.Name #>">
        <Annotations>
          <Annotation AnnotationType="Tag" Tag="TargetName"><#= db.GetTag("TargetName") #></Annotation>
        </Annotations>
      </Schema>
    <# Next #>
  </Schemas>
</Biml>

Tier 4: Tables

For every source connection, the table tier reads the list of required tables from the control view and builds a Table node in the matching target schema. Annotations record the original source table name, the target name, and the source connection name:

<#@ template language="VB" tier="4" #>
<#@ code file="../Code/DeployHelpers.vb" #>
<#@ import namespace="DeployHelpers" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Tables>
    <#
      For Each srcDb As AstConnectionBaseNode In RootNode.Connections.Where(Function(c) c.GetTag("Type") = "Source")
        For Each tbl As AstTableNode In srcDb.GetDatabaseSchema(Nothing, GetNonEmptyList(RootNode.Connections("Control"), "Select TableName from [vDeployMeta_Tables] where ConnectionName = '" & srcDb.Name & "'"), DefaultImportOptions).TableNodes
    #>
    <Table Name="<#= srcDb.GetTag("BaseConnection") #>_<#= tbl.Schema.Name #>_<#= tbl.Name #>" SchemaName="<#= RootNode.Schemas.Where(Function(c) c.GetTag("TargetName") = srcDb.GetTag("TargetName")).First.ScopedName #>">
      <Columns>
        <#= tbl.Columns.GetBiml() #>
      </Columns>
      <Annotations>
        <Annotation AnnotationType="Tag" Tag="TableName"><#= tbl.ScopedName #></Annotation>
        <Annotation AnnotationType="Tag" Tag="TargetName"><#= srcDb.GetTag("TargetName") #></Annotation>
        <Annotation AnnotationType="Tag" Tag="SrcName"><#= srcDb.Name #></Annotation>
      </Annotations>
    </Table>
    <#
        Next
      Next
    #>
  </Tables>
</Biml>

Tier 5 and 6: Create and Populate Packages

A package per target produces the CREATE statements:

<#@ template language="VB" tier="5" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Packages>
    <# For Each target As AstConnectionBaseNode In RootNode.Connections.Where(Function(c) c.GetTag("Type") = "Target") #>
      <Package Name="Create_Staging_<#= target.GetTag("TargetName") #>" PackageSubpath="<#= target.GetTag("TargetName") #>">
        <Tasks>
          <# For Each tbl As AstTableNode In RootNode.Tables.Where(Function(c) c.GetTag("TargetName").Trim = target.GetTag("TargetName").Trim) #>
            <ExecuteSQL Name="Create <#= tbl.Name #>" ConnectionName="<#= target.Name #>">
              <DirectInput>
                <#= tbl.GetDropAndCreateDdl() #>
              </DirectInput>
            </ExecuteSQL>
          <# Next #>
        </Tasks>
      </Package>
    <# Next #>
  </Packages>
</Biml>

A second package per target truncates and reloads each table:

<#@ template language="VB" tier="6" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Packages>
    <# For Each target As AstConnectionBaseNode In RootNode.Connections.Where(Function(c) c.GetTag("Type") = "Target") #>
      <Package Name="Populate_Staging_<#= target.GetTag("TargetName") #>" PackageSubpath="<#= target.GetTag("TargetName") #>">
        <Tasks>
          <# For Each tbl As AstTableNode In RootNode.Tables.Where(Function(c) c.GetTag("TargetName").Trim = target.GetTag("TargetName").Trim) #>
            <Container Name="Transfer <#= tbl.Name #>" ConstraintMode="Linear">
              <Tasks>
                <ExecuteSQL Name="Truncate" ConnectionName="<#= target #>">
                  <DirectInput>truncate table <#= tbl.ScopedName #></DirectInput>
                </ExecuteSQL>
                <Dataflow Name="Copy <#= tbl.Name #>">
                  <Transformations>
                    <OleDbSource Name="Get Rows" ConnectionName="<#= tbl.GetTag("SrcName").Trim #>">
                      <DirectInput>SELECT <#= tbl.GetColumnList() #> FROM <#= tbl.GetTag("TableName") #></DirectInput>
                    </OleDbSource>
                    <OleDbDestination Name="Set Rows" ConnectionName="<#= target #>">
                      <TableOutput TableName="<#= tbl.ScopedName #>" />
                    </OleDbDestination>
                  </Transformations>
                </Dataflow>
              </Tasks>
            </Container>
          <# Next #>
        </Tasks>
      </Package>
    <# Next #>
  </Packages>
</Biml>

The result is a deployment surface controlled entirely by metadata: flipping IsActive on a target row removes it from the next regenerated build, adding a new row in DeployMeta_Targets adds a complete set of packages for that target.

Why Metadata Driven Deployment​

The Metadata Schema​

A Reusable Code File​

Tier 1: Connections​

Tier 2 and 3: Databases and Schemas​

Tier 4: Tables​

Tier 5 and 6: Create and Populate Packages​