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
- Create a folder named
BimlQuickStart - Open the folder in Visual Studio Code
Step 2: Add a New Biml File
- In the Explorer, create a new file in the folder
- 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=localhostto your SQL Server instance - Change
AdventureWorks2019to your database name
Step 4: Generate SSIS Packages
- In the Explorer, right-click
LoadData.biml - Select Build Biml
- BimlExpress compiles the Biml and writes
LoadSampleData.dtsxto theoutput/ssisfolder 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.
- Add
LoadSampleData.dtsxfrom theoutput/ssisfolder to an Integration Services project in that tool - Open
LoadSampleData.dtsxin the SSIS designer - Verify the data flow and tasks look correct
- 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 | Run Open Last Build Output from the Command Palette and check for BimlScript syntax errors |
| Build errors | Right-click the file in the Explorer and click Validate Biml before building |
Next Steps
Now that you've built your first Biml solution:
- Biml Basics - Learn syntax and conventions
- Common Patterns - Reusable solutions for staging, loading
- Biml for SSIS - Deep dive into SSIS generation
- 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" #>