Skip to main content

Quick Start Guide

Build your first automated SSIS package in 5 minutes.

Prerequisites​

Before you begin, ensure you have:

  • Visual Studio Code with BimlExpress for Visual Studio Code installed (BimlExpress for Visual Studio Code lists the requirements and how to install it)
  • SQL Server with a sample database (e.g., AdventureWorks)

The samples on this page use OLE DB connections. On macOS and Linux, a build that needs OLE DB providers says that it needs the Windows build worker. The Build Worker explains what runs where.

When your session is not licensed, BimlExpress shows an activation prompt with options to enter or request a product key. Account and Licensing describes your account and product key.

Step 1: Open a Folder​

  1. Create a folder named BimlQuickStart
  2. Open the folder in Visual Studio Code

Step 2: Add a New Biml File​

  1. In the Explorer, create a new file in the folder
  2. Name it LoadData.biml

BimlExpress treats every file with the .biml extension as a Biml file.

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 the Explorer, right-click LoadData.biml
  2. Select Build Biml
  3. BimlExpress compiles the Biml and writes LoadSampleData.dtsx to the output/ssis folder of your workspace

You should see the new package in the output/ssis folder in the Explorer. The package targets SSIS 2019 unless you change the Ssis: Version setting. Build and Validate describes the output folders and settings.

Step 5: Run Your Package​

Open and run the package in your SSIS development tool, such as Visual Studio with the SQL Server Integration Services Projects extension, which is separate from BimlExpress.

  1. Add LoadSampleData.dtsx from the output/ssis folder to an Integration Services project in that tool
  2. Open LoadSampleData.dtsx in the SSIS designer
  3. Verify the data flow and tasks look correct
  4. 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​

IssueSolution
"Cannot find connection"Ensure connection names match exactly (case-sensitive)
"Table not found"Verify table exists and connection string is correct
No packages generatedRun Open Last Build Output from the Command Palette and check for BimlScript syntax errors
Build errorsRight-click the file in the Explorer and click Validate Biml before building

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​

SyntaxPurposeExample
<# ... #>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" #>