Skip to main content

Introduction to Biml for SSIS: Build a Package

What Biml Is​

Biml stands for Business Intelligence Markup Language. It is a dialect of XML that describes BI assets like SSIS packages, SSAS cubes, and database objects. A Biml file is human readable and can be checked into source control, compared in a diff tool, and regenerated as needed. The compiler turns Biml into ordinary .dtsx packages that any SSIS developer can open in Visual Studio.

Two tools compile Biml. BimlExpress is the free Visual Studio add-in. BimlStudio is the full IDE from Varigence. The walkthrough below uses the free path.

The Empty File​

A new Biml file in an SSIS project starts as a single root element:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
</Biml>

That namespace identifies the document as Biml so the compiler can validate it.

Demo Tables​

The example moves rows from a source table to a destination table with the same shape. Two tables are created in a database called Workshop:

CREATE TABLE dbo.AssociateSource
(
  AssociateId INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_AssociateSource PRIMARY KEY CLUSTERED (AssociateId),
  GivenName   NVARCHAR(50)  NULL,
  FamilyName  NVARCHAR(100) NULL,
  Notes       NVARCHAR(255) NULL,
  LoadDate    DATETIME      NULL
);

CREATE TABLE dbo.AssociateTarget
(
  AssociateId INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_AssociateTarget PRIMARY KEY CLUSTERED (AssociateId),
  GivenName   NVARCHAR(50)  NULL,
  FamilyName  NVARCHAR(100) NULL,
  Notes       NVARCHAR(255) NULL,
  LoadDate    DATETIME      NULL
);

INSERT INTO dbo.AssociateSource (GivenName, FamilyName) VALUES
  ('Avery', 'Lin'),
  ('Jordan', 'Ortiz'),
  ('Sam', 'Patel');

Adding a Connection​

A Connections collection holds connection managers for the package. Each Connection has a name and a connection string:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <Connection Name="WorkshopDB"
      ConnectionString="Data Source=localhost;Initial Catalog=Workshop;Provider=SQLNCLI11.1;Integrated Security=SSPI;" />
  </Connections>
</Biml>

The same connection name is referenced from each task and component that needs the database.

Adding a Package and a Task​

A Packages collection holds Package elements. Each Package has Tasks, which translate to control flow components in the SSIS designer. The Biml below adds an Execute SQL task that returns a row count:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <Connection Name="WorkshopDB"
      ConnectionString="Data Source=localhost;Initial Catalog=Workshop;Provider=SQLNCLI11.1;Integrated Security=SSPI;" />
  </Connections>
  <Packages>
    <Package Name="MoveAssociates" ConstraintMode="Linear">
      <Tasks>
        <ExecuteSQL Name="Count Associates" ConnectionName="WorkshopDB">
          <DirectInput>
            SELECT COUNT(1) AS AssociateTotal
            FROM dbo.AssociateSource
          </DirectInput>
        </ExecuteSQL>
      </Tasks>
    </Package>
  </Packages>
</Biml>

ConstraintMode set to Linear tells the compiler to chain tasks together in declaration order. Right-clicking the .biml file and choosing Generate SSIS Packages produces a MoveAssociates.dtsx file.

Adding a Data Flow​

A Data Flow lives inside the Tasks block. Inside the data flow, the components live in a Transformations block. The next block adds an OLE DB source after the Execute SQL task:

<Dataflow Name="Extract Associates">
  <Transformations>
    <OleDbSource Name="Associate Source" ConnectionName="WorkshopDB">
      <DirectInput>
        SELECT AssociateId, GivenName, FamilyName, Notes, LoadDate
        FROM dbo.AssociateSource
      </DirectInput>
    </OleDbSource>
  </Transformations>
</Dataflow>

The DirectInput element holds exactly what would be typed into the SQL Command box in the OLE DB Source Editor in the SSIS UI.

Adding a Derived Column and a Destination​

A DerivedColumns transformation can replace an existing column or add a new one. The OleDbDestination writes the rows out:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <Connection Name="WorkshopDB"
      ConnectionString="Data Source=localhost;Initial Catalog=Workshop;Provider=SQLNCLI11.1;Integrated Security=SSPI;" />
  </Connections>
  <Packages>
    <Package Name="MoveAssociates" ConstraintMode="Linear">
      <Tasks>
        <ExecuteSQL Name="Count Associates" ConnectionName="WorkshopDB">
          <DirectInput>
            SELECT COUNT(1) AS AssociateTotal
            FROM dbo.AssociateSource
          </DirectInput>
        </ExecuteSQL>
        <Dataflow Name="Extract Associates">
          <Transformations>
            <OleDbSource Name="Associate Source" ConnectionName="WorkshopDB">
              <DirectInput>
                SELECT AssociateId, GivenName, FamilyName, Notes, LoadDate
                FROM dbo.AssociateSource
              </DirectInput>
            </OleDbSource>
            <DerivedColumns Name="Stamp Columns">
              <Columns>
                <Column Name="LoadDate" DataType="DateTime" ReplaceExisting="true">
                  GETDATE()
                </Column>
                <Column Name="Notes" DataType="String" ReplaceExisting="true">
                  "Loaded via SSIS"
                </Column>
              </Columns>
            </DerivedColumns>
            <OleDbDestination Name="Insert Associates" ConnectionName="WorkshopDB">
              <ExternalTableOutput Table="dbo.AssociateTarget" />
            </OleDbDestination>
          </Transformations>
        </Dataflow>
      </Tasks>
    </Package>
  </Packages>
</Biml>

Each Column inside DerivedColumns carries the expression as its inner text, which mirrors the Expression box on the Derived Column component in the SSIS designer. Biml is case sensitive, the same as XML in general.

What the Generated Package Looks Like​

Generating the package and opening it in Visual Studio shows a control flow with the Execute SQL task on top and the Data Flow underneath, connected by a precedence constraint. The data flow shows OLE DB Source, Derived Column, and OLE DB Destination, connected by data flow paths. The package runs and inserts three rows into AssociateTarget.

Why This Matters​

A single Biml package looks like work for the same outcome as building a package by hand. The reason to learn the syntax first is so the metadata-driven layer makes sense later. Once a single package can be expressed in Biml, the same Biml can be wrapped in a loop that emits one such package per source table, per environment, or per load pattern. The single-package syntax shown here is the building block for that automation.