Skip to main content

Asset Declaration and Scripting Basics in Biml: Part 2

Defining the Environment​

Biml is more than a package generator. The same XML can declare connections, databases, schemas, and tables as first-class environment objects. Once declared, those objects can be referenced from package generation code as metadata. The first generator file declares the environment with a tier directive that forces it to build before the package file. Tables can be declared with or without column lists.

<#@ template tier="1" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <OleDbConnection Name="stage" ConnectionString="Provider=SQLOLEDB;Data Source=.\sql2019;Integrated Security=SSPI;Initial Catalog=RetailStage" />
    <OleDbConnection Name="retail_source" ConnectionString="Provider=SQLOLEDB;Data Source=.\sql2019;Integrated Security=SSPI;Initial Catalog=RetailSource" />
  </Connections>

  <Databases>
    <Database ConnectionName="stage" Name="stage" />
  </Databases>

  <Schemas>
    <Schema DatabaseName="stage" Name="dbo" />
  </Schemas>

  <Tables>
    <Table Name="Item" SchemaName="stage.dbo" />
    <Table Name="ItemSubCategory" SchemaName="stage.dbo" />
    <Table Name="ItemCategory" SchemaName="stage.dbo">
      <Columns>
        <Column Name="ItemCategoryID" DataType="Int32" />
        <Column Name="Name" DataType="String" Length="50" />
      </Columns>
    </Table>
  </Tables>
</Biml>

The Database, Schema, and Table elements form a declared environment. Since the package generator below depends on those tables existing, the tier directive guarantees compile order.

Generating Packages from RootNode​

The second file declares tier 2 and walks RootNode.Tables to build a package. For each table, the script emits a Truncate task and a Dataflow that loads the matching staging table. RootNode is the entry point to all assets defined in the upstream files.

<#@ template tier="2" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Packages>
    <Package Name="build_stage" ConstraintMode="Parallel" ProtectionLevel="EncryptSensitiveWithUserKey">
      <Tasks>
        <# foreach (var tbl in RootNode.Tables) { #>
        <ExecuteSQL Name="TRUNC_<#= tbl #>" ConnectionName="stage">
          <DirectInput>Truncate Table dbo.import_<#= tbl #>;</DirectInput>
        </ExecuteSQL>

        <Dataflow Name="LOAD_<#= tbl #>">
          <Transformations>
            <OleDbSource Name="SRC_<#= tbl #>" ConnectionName="retail_source">
              <DirectInput>Select * from Production.<#= tbl #>;</DirectInput>
            </OleDbSource>
            <OleDbDestination Name="TGT_<#= tbl #>" ConnectionName="stage">
              <ExternalTableOutput Table="dbo.import_<#= tbl #>" />
            </OleDbDestination>
          </Transformations>
          <PrecedenceConstraints>
            <Inputs>
              <Input OutputPathName="TRUNC_<#= tbl #>.Output" />
            </Inputs>
          </PrecedenceConstraints>
        </Dataflow>
        <# } #>
      </Tasks>
    </Package>
  </Packages>
</Biml>

Two things are worth pointing out. The foreach over RootNode.Tables works because tables were declared in the tier 1 file. The expression code nugget that prints the table name uses the equal sign syntax, which prints whatever expression is between the open and close tags. Anywhere the original template said Item, the script substitutes the current table name.

Generating Multiple Files Together​

Both files are generated at once. The tier numbers force the environment file to compile first so the package file can rely on the declared assets. Highlighting both files and choosing Generate SSIS Packages produces the correct order automatically.

Wrap-Up​

This pattern lets the same project specify the environment and generate packages from it. Compared to embedding metadata in code, declared assets stay readable and can be split across multiple files for parallel work.