A Minimalistic Approach to Processing Packages with Biml

Why Keep the Metadata Small

Most metadata driven Biml examples cover staging and load. Processing packages, the ones that fire stored procedures and process SSAS databases, often get hand built. They do not have to. A tiny metadata model can describe both connections and ordered task lists, and a Biml script can build matching SSIS packages. The model below is deliberately small so it stays easy to maintain, but it scales to multiple projects.

The Metadata Model

Two tables and one view describe everything. Connections record where to run, packages record what to run and in which order:

USE [ProcessingControl]
GO
CREATE TABLE [dbo].[ProcMeta_Connections] (
  [Connection] [nvarchar](50) NULL,
  [Type] [nvarchar](50) NULL,
  [Server] [nvarchar](50) NULL,
  [Database] [nvarchar](50) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ProcMeta_Packages] (
  [Package] [nvarchar](50) NULL,
  [Tier] [int] NULL,
  [StepName] [nvarchar](50) NULL,
  [StepType] [nvarchar](50) NULL,
  [Connection] [nvarchar](50) NULL,
  [Task] [nvarchar](50) NULL,
  [Parameter_1] [nvarchar](50) NULL,
  [Parameter_2] [nvarchar](50) NULL
) ON [PRIMARY]
GO
CREATE VIEW [dbo].[vProcMeta_Packages] AS
  SELECT c.Connection
    , c.[Database]
    , p.Package
    , p.Tier
    , p.StepName
    , p.StepType
    , ISNULL(p.Task, N'') AS Task
    , ISNULL(p.Parameter_1, N'') AS Parameter_1
    , ISNULL(p.Parameter_2, N'') AS Parameter_2
  FROM dbo.ProcMeta_Connections AS c
  INNER JOIN dbo.ProcMeta_Packages AS p ON c.Connection = p.Connection
GO
INSERT INTO [ProcMeta_Connections] SELECT 'CubeServer','OLAP','DwhServer','SalesCube'
INSERT INTO [ProcMeta_Connections] SELECT 'Warehouse','OLEDB','DwhServer','SalesWarehouse'
INSERT INTO [ProcMeta_Packages] SELECT 'OnlyOneProc',1,'Proc 1','SP','Warehouse','sp_RebuildSummary','100',''
INSERT INTO [ProcMeta_Packages] SELECT 'FullProcess',1,'Proc 1','SP','Warehouse','sp_RebuildSummary','100',''
INSERT INTO [ProcMeta_Packages] SELECT 'FullProcess',1,'Proc 2','SP','Warehouse','sp_LoadDim','''A'',20',''
INSERT INTO [ProcMeta_Packages] SELECT 'FullProcess',2,'Proc 3','SP','Warehouse','sp_LoadFact','5',''
INSERT INTO [ProcMeta_Packages] SELECT 'FullProcess',3,'Process Cube','OLAPDB','CubeServer','','',''

Connection rows declare a connection name, a type (OLEDB or OLAP), the server, and the database. Package rows declare the package name, the tier within the package, a step name, the step type (SP or OLAPDB), the connection to use, the task body (typically a stored procedure name), and up to two free form parameters. All rows sharing a package name go into the same package, all rows sharing a tier inside that package run in parallel, and tiers run in order.

Building Connections

The first tier loops the connection table and emits the right Biml node depending on type. OLEDB rows produce an OleDbConnection, OLAP rows produce an AnalysisServicesConnection:

<#@ template language="VB" tier="1" #>
<#@ import namespace="System.Data" #>
<# Dim controlConnString As String = "Provider=SQLNCLI11;Server=Localhost;Initial Catalog=ProcessingControl;Integrated Security=SSPI;" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <OleDbConnection Name="Control" ConnectionString="<#= controlConnString #>" />
    <#
      For Each dr As DataRow In ExternalDataAccess.GetDataTable(controlConnString, "Select * from ProcMeta_Connections").Rows
        If dr("Type") = "OLEDB" Then
    #>
      <OleDbConnection Name="<#= dr("Connection") #>" ConnectionString="Provider=SQLNCLI11;Server=<#= dr("Server") #>;Initial Catalog=<#= dr("Database") #>;Integrated Security=SSPI;" />
    <#
        ElseIf dr("Type") = "OLAP" Then
    #>
      <AnalysisServicesConnection Name="<#= dr("Connection") #>" Server="<#= dr("Server") #>" ConnectionString="Data Source=<#= dr("Server") #>;Initial Catalog=<#= dr("Database") #>;Provider=MSOLAP.6;Integrated Security=SSPI;Impersonation Level=Impersonate;" />
    <#
        End If
      Next
    #>
  </Connections>
</Biml>

Adding a new connection type means adding one elseif branch.

Building Packages, Tiers, and Tasks

The second tier reads the control connection back out of the root node and queries the packages view to drive three nested loops: one per distinct package name, one per distinct tier inside that package, and one per task inside that tier. Containers run linearly to honor tier order, while tasks inside a container run in parallel:

<#@ template language="VB" tier="2" #>
<#@ import namespace="System.Data" #>
<#
  Dim controlConn As AstDbConnectionNode = RootNode.Connections("Control")
  Dim controlConnString As String = controlConn.ConnectionString
#>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Packages>
    <# For Each pkg As DataRow In ExternalDataAccess.GetDataTable(controlConnString, "Select distinct Package from [vProcMeta_Packages]").Rows #>
      <Package Name="<#= pkg("Package") #>" ConstraintMode="Linear">
        <Tasks>
          <# For Each tier As DataRow In ExternalDataAccess.GetDataTable(controlConnString, "Select distinct Tier from [vProcMeta_Packages] where Package = '" & pkg("Package") & "' order by Tier").Rows #>
            <Container Name="Tier <#= tier(0) #>" ConstraintMode="Parallel">
              <Tasks>
                <# For Each task As DataRow In ExternalDataAccess.GetDataTable(controlConnString, "Select * from [vProcMeta_Packages] where Package = '" & pkg("Package") & "' and Tier = " & tier(0)).Rows #>
                  <# If task("StepType") = "SP" Then #>
                    <ExecuteSQL Name="<#= task("StepName") #>" ConnectionName="<#= task("Connection") #>">
                      <DirectInput>
                        EXEC <#= task("Task") #> <#= task("Parameter_1") #>
                      </DirectInput>
                    </ExecuteSQL>
                  <# ElseIf task("StepType") = "OLAPDB" Then #>
                    <AnalysisServicesProcessing Name="<#= task("StepName") #>" ConnectionName="<#= task("Connection") #>">
                      <ProcessingConfigurations>
                        <DatabaseProcessingConfiguration DatabaseId="<#= task("Database") #>" ProcessingOption="ProcessFull" />
                      </ProcessingConfigurations>
                    </AnalysisServicesProcessing>
                  <# End If #>
                <# Next #>
              </Tasks>
            </Container>
          <# Next #>
        </Tasks>
      </Package>
    <# Next #>
  </Packages>
</Biml>

The if elseif block is the extension point. Adding dimension processing, partition processing, backups, or any other task type means adding one more branch and (if the task uses a new connection type) one more entry in the connections tier.

The result is a single Biml project that produces a complete processing layer for any number of orchestration packages, all driven by a few rows of metadata.