Quick Start Guide

Build your first automated SSIS package in 5 minutes.

Prerequisites

Before you begin, ensure you have:

• Visual Studio 2019 or 2022 with SQL Server Data Tools (SSDT)
• BimlExpress extension installed (Download free from Varigence)
• SQL Server with a sample database (e.g., AdventureWorks)

Step 1: Create an SSIS Project

1. Open Visual Studio
2. Select File > New > Project
3. Choose Integration Services Project
4. Name it BimlQuickStart and click Create

Step 2: Add a New Biml File

1. In Solution Explorer, right-click the project
2. Select Add > New Item
3. Choose Biml File (or create a text file and rename it to Package.biml)
4. Name it LoadData.biml

Step 3: Write Your First Biml

Replace the contents of LoadData.biml with this code:

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

  <Packages>
    <Package Name="LoadSampleData" ConstraintMode="Linear">
      <Tasks>
        <ExecuteSQL Name="LogStart" ConnectionName="Source">
          <DirectInput>
      PRINT 'Package started at ' + CONVERT(VARCHAR, GETDATE(), 120)
          </DirectInput>
        </ExecuteSQL>

        <Dataflow Name="LoadPersonData">
          <Transformations>
            <OleDbSource Name="GetPersons" ConnectionName="Source">
              <DirectInput>
        SELECT TOP 100
         BusinessEntityID,
         FirstName,
         LastName
        FROM Person.Person
              </DirectInput>
            </OleDbSource>

            <RowCount Name="CountRows" VariableName="User.RowCount" />

            <OleDbDestination Name="InsertPersons" ConnectionName="Source">
              <ExternalTableOutput Table="[dbo].[StagePerson]" />
            </OleDbDestination>
          </Transformations>
        </Dataflow>

        <ExecuteSQL Name="LogEnd" ConnectionName="Source">
          <DirectInput>
      PRINT 'Package completed at ' + CONVERT(VARCHAR, GETDATE(), 120)
          </DirectInput>
        </ExecuteSQL>
      </Tasks>

      <Variables>
        <Variable Name="RowCount" DataType="Int32">0</Variable>
      </Variables>
    </Package>
  </Packages>
</Biml>

Customize for your environment:

• Update Data Source=localhost to your SQL Server instance
• Change AdventureWorks2019 to your database name

Step 4: Generate SSIS Packages

1. In Solution Explorer, right-click LoadData.biml
2. Select Generate SSIS Packages
3. BimlExpress compiles the Biml and creates LoadSampleData.dtsx

You should see a new package appear in your SSIS project.

Step 5: Run Your Package

1. Double-click LoadSampleData.dtsx to open in the designer
2. Verify the data flow and tasks look correct
3. Press F5 or click Start to execute the package

What Just Happened?

You wrote ~40 lines of Biml that generated a complete SSIS package with:

• Connection manager
• Execute SQL tasks for logging
• Data flow with source, row count, and destination
• Package variable

Key insight: This same pattern scales to generate hundreds of packages from a template.

Add Automation with Loops

Now make it dynamic. Create a new file LoadAllTables.biml:

<#@ import namespace="Varigence.Biml.CoreLowerer.SchemaManagement" #>
<#
// Get tables from source database
var sourceConnection = RootNode.OleDbConnections["Source"];
var tables = sourceConnection.GetDatabaseSchema(
 new List<string>{"Person"}, // Only Person schema
 null,
 ImportOptions.ExcludeViews
).TableNodes.Take(5); // Limit to 5 tables for this example
#>

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Packages>
    <# foreach (var table in tables) { #>
    <Package Name="Load_<#= table.Schema #>_<#= table.Name #>" ConstraintMode="Linear">
      <Tasks>
        <Dataflow Name="LoadData">
          <Transformations>
            <OleDbSource Name="Source" ConnectionName="Source">
              <DirectInput>SELECT * FROM [<#= table.Schema #>].[<#= table.Name #>]</DirectInput>
            </OleDbSource>
            <FlatFileDestination Name="Destination" ConnectionName="Output_<#= table.Name #>" />
          </Transformations>
        </Dataflow>
      </Tasks>
    </Package>
    <# } #>
  </Packages>
</Biml>

Result: One template generates a separate package for each table.

Common Issues and Solutions

Issue	Solution
"Cannot find connection"	Ensure connection names match exactly (case-sensitive)
"Table not found"	Verify table exists and connection string is correct
No packages generated	Check for BimlScript syntax errors in Output window
Build errors	Right-click > Check Biml for Errors before generating

Next Steps

Now that you've built your first Biml solution:

1. Biml Basics - Learn syntax and conventions
2. Common Patterns - Reusable solutions for staging, loading
3. Biml for SSIS - Deep dive into SSIS generation
4. Build Staging Layer - Complete end-to-end example

Quick Reference

BimlScript Nugget Types

Syntax	Purpose	Example
<# ... #>	Execute C# code	<# foreach (var t in tables) { #>
<#= ... #>	Output value	<#= table.Name #>
<#@ ... #>	Directive	<#@ import namespace="..." #>

Essential Directives

<!-- Import namespaces -->
<#@ import namespace="Varigence.Biml.CoreLowerer.SchemaManagement" #>

<!-- Set tier for compilation order -->
<#@ template tier="20" #>

<!-- Include other Biml files -->
<#@ include file="Connections.biml" #>