Loading lesson map...

Biml Basics for SSIS

Generate SSIS packages programmatically using Biml. This guide covers fundamentals and automation patterns that reduce development time from weeks to hours.

Why Use Biml for SSIS

Task	Manual Approach	Biml Approach
Create 100 staging packages	2-3 weeks	1-2 hours
Add error handling to all packages	Edit each package manually	Update template, regenerate
Handle source schema changes	Find and update affected packages	Regenerate from metadata
Implement consistent logging	Copy/paste into each package	Define once, apply everywhere

Key benefits:

• Consistency: Every package follows the same pattern
• Maintainability: Change the template, regenerate all packages
• Speed: Generate hundreds of packages in seconds
• Quality: Eliminate manual errors and missed configurations

Prerequisites

Before starting, complete these guides:

• Biml Basics - Core syntax and naming conventions
• Biml for Relational DBs - Connections and table definitions

Quick Start: Your First Package

Create a package that loads data from source to destination:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <OleDbConnection Name="Source"
      ConnectionString="Provider=SQLNCLI11;Server=localhost;Initial Catalog=AdventureWorks2012;Integrated Security=SSPI;" />
    <OleDbConnection Name="Target"
      ConnectionString="Provider=SQLNCLI11;Server=localhost;Initial Catalog=Staging;Integrated Security=SSPI;" />
  </Connections>
  <Packages>
    <Package Name="LoadCustomer" ConstraintMode="Linear">
      <Tasks>
        <ExecuteSQL Name="TruncateTarget" ConnectionName="Target">
          <DirectInput>TRUNCATE TABLE dbo.Customer</DirectInput>
        </ExecuteSQL>
        <Dataflow Name="LoadData">
          <Transformations>
            <OleDbSource Name="Source" ConnectionName="Source">
              <DirectInput>SELECT * FROM Sales.Customer</DirectInput>
            </OleDbSource>
            <OleDbDestination Name="Target" ConnectionName="Target">
              <ExternalTableOutput Table="dbo.Customer" />
            </OleDbDestination>
          </Transformations>
        </Dataflow>
      </Tasks>
    </Package>
  </Packages>
</Biml>

Build this Biml to generate a working SSIS package with truncate-and-load logic.

---

Control Flow

Control flow defines the execution order of tasks in your package. Add tasks within the <Tasks> collection:

<Package Name="Package1">
  <Tasks>
    <ExecuteSQL Name="RunStoredProcedure" ConnectionName="Connection1">
      <DirectInput>EXEC dbo.uspLogError</DirectInput>
    </ExecuteSQL>
  </Tasks>
</Package>

Essential Control Flow Tasks

Task	Purpose	Common Use Case
ExecuteSQL	Run SQL statements	Truncate tables, run procedures
Dataflow	Move and transform data	Source to destination loads
ExecutePackage	Run child packages	Orchestrate workflows
Container	Group tasks	Organize parallel/sequential tasks
ForEachFileLoop	Iterate over files	Process CSV files from folder
ForLoop	Loop with counter	Batch processing
Script	Custom .NET code	Complex logic, API calls

Supported Control Flow Tasks

Biml supports 40+ SSIS task types. Common tasks organized by category:

Category	Tasks
Data Movement	Dataflow, BulkInsert, TransferDatabase, TransferSqlServerObjects
SQL Execution	ExecuteSQL, ExecuteTSqlStatement
File Operations	FileSystem, Ftp, TransferErrorMessages
Package Control	ExecutePackage, ForLoop, ForEachLoop, Container, RetryContainer
Database Maintenance	BackupDatabase, RebuildIndex, ReorganizeIndex, UpdateStatistics, ShrinkDatabase, CheckDatabaseIntegrity, HistoryCleanup, MaintenanceCleanup
Analysis Services	AnalysisServicesExecuteDdl, AnalysisServicesProcessing
Notifications	SendMail, MessageQueue, NotifyOperator
Scripting	Script, Expression
Transfer Tasks	TransferJobs, TransferLogins, TransferMasterStoredProcedures

For the complete list with all attributes, see the Biml Language Reference.

Custom and Third-Party Tasks

Use CustomTask to reference tasks from third-party libraries (e.g., CozyRoc, TaskFactory):

<CustomTask Name="SFTPDownload"
  CreationName="COZYROC.SSISPlus.SFTPTask">
  <ObjectData>
    <!-- Task-specific configuration as CDATA XML -->
  </ObjectData>
</CustomTask>

Note: Third-party tasks require the component installed on both build and execution machines. Check vendor documentation for the correct CreationName value.

Precedence Constraints

Control task execution order with precedence constraints.

Linear mode (most common): Tasks run sequentially on success:

<Package Name="Package1" ConstraintMode="Linear">
  <Tasks>
    <ExecuteSQL Name="Step1" ConnectionName="Conn1">
      <DirectInput>SELECT 1</DirectInput>
    </ExecuteSQL>
    <ExecuteSQL Name="Step2" ConnectionName="Conn1">
      <DirectInput>SELECT 2</DirectInput>
    </ExecuteSQL>
    <!-- Step2 runs only after Step1 succeeds -->
  </Tasks>
</Package>

Constraint Conditions

Control task execution based on the outcome of previous tasks:

Condition	Executes When	Use Case
Success	Previous task succeeded	Normal workflow progression
Failure	Previous task failed	Error handling, notifications
Completion	Previous task completed (success or failure)	Cleanup tasks, logging

Example: Conditional Execution

<Package Name="LoadWithLogging" ConstraintMode="Parallel">
  <Tasks>
    <ExecuteSQL Name="LoadData" ConnectionName="Target">
      <DirectInput>EXEC usp_LoadCustomer</DirectInput>
    </ExecuteSQL>

    <ExecuteSQL Name="LogSuccess" ConnectionName="Target">
      <DirectInput>INSERT INTO LoadLog VALUES ('Success', GETDATE())</DirectInput>
      <PrecedenceConstraints>
        <Inputs>
          <Input OutputPathName="LoadData.Output" EvaluationOperation="Constraint" EvaluationValue="Success"/>
        </Inputs>
      </PrecedenceConstraints>
    </ExecuteSQL>

    <ExecuteSQL Name="LogFailure" ConnectionName="Target">
      <DirectInput>INSERT INTO LoadLog VALUES ('Failed', GETDATE())</DirectInput>
      <PrecedenceConstraints>
        <Inputs>
          <Input OutputPathName="LoadData.Output" EvaluationOperation="Constraint" EvaluationValue="Failure"/>
        </Inputs>
      </PrecedenceConstraints>
    </ExecuteSQL>
  </Tasks>
</Package>

Expression-Based Constraints

Combine constraint value with expressions for complex logic:

<PrecedenceConstraints>
  <Inputs>
    <Input OutputPathName="Task1.Output"
      EvaluationOperation="ExpressionAndConstraint"
      EvaluationValue="Success">
      <Expression>@[User::RowCount] > 0</Expression>
    </Input>
  </Inputs>
</PrecedenceConstraints>

This executes only if Task1 succeeds AND RowCount is greater than zero.

Automation Tip: Generate Sequential Tasks

Use BimlScript loops with ConstraintMode="Linear" to automatically chain generated tasks:

<Package Name="LoadAll" ConstraintMode="Linear">
  <Tasks>
    <# foreach (var table in RootNode.Tables) { #>
    <Dataflow Name="Load_<#= table.Name #>">
      <!-- Each generated task automatically connects to the previous -->
    </Dataflow>
    <# } #>
  </Tasks>
</Package>

---

Data Flow

Data flow tasks move data row-by-row through source, transformation, and destination components.

<Dataflow Name="LoadData">
  <Transformations>
    <OleDbSource Name="Source" ConnectionName="SourceConn">
      <DirectInput>SELECT * FROM dbo.Customer</DirectInput>
    </OleDbSource>
    <DerivedColumns Name="AddAuditColumns">
      <Columns>
        <Column Name="LoadDateTime" DataType="DateTime">(DT_DBTIMESTAMP)GETDATE()</Column>
      </Columns>
    </DerivedColumns>
    <OleDbDestination Name="Target" ConnectionName="TargetConn">
      <ExternalTableOutput Table="dbo.Customer" />
    </OleDbDestination>
  </Transformations>
</Dataflow>

Essential Data Flow Components

Sources:

Component	Purpose
OleDbSource	SQL Server, Oracle, etc.
FlatFileSource	CSV, text files
ExcelSource	Excel spreadsheets
AdoNetSource	.NET data providers

Transformations:

Component	Purpose
DerivedColumns	Add/modify columns
Lookup	Reference data joins
ConditionalSplit	Route rows by condition
Aggregate	GROUP BY operations
Sort	Order rows
MergeJoin	Join sorted inputs
UnionAll	Combine multiple inputs

Destinations:

Component	Purpose
OleDbDestination	Database tables
FlatFileDestination	CSV, text files
ExcelDestination	Excel spreadsheets

For all 50+ components, see the Biml Language Reference.

Dataflow Paths

Implicit paths (recommended): Components connect automatically in order:

<Transformations>
  <OleDbSource Name="Source" ConnectionName="Conn">
    <DirectInput>SELECT * FROM dbo.Table1</DirectInput>
  </OleDbSource>
  <!-- Automatically connects to Source output -->
  <OleDbDestination Name="Target" ConnectionName="Conn">
    <ExternalTableOutput Table="dbo.Table1" />
  </OleDbDestination>
</Transformations>

Explicit paths for non-standard routing (e.g., error output):

<OleDbDestination Name="Target" ConnectionName="Conn">
  <ExternalTableOutput Table="dbo.Table1" />
  <InputPath OutputPathName="Source.Output" />
</OleDbDestination>

---

Automation Patterns

The real power of Biml emerges when you automate package generation. See Common Patterns for complete working examples.

Pattern 1: Metadata-Driven Generation

Generate one package per table using GetDatabaseSchema():

<#@ template tier="20" #>
<#@ import namespace="Varigence.Biml.CoreLowerer.SchemaManagement" #>
<#
var sourceConn = RootNode.OleDbConnections["Source"];
var tables = sourceConn.GetDatabaseSchema(
  new List<string>{"Sales", "Production"},
  null,
  ImportOptions.ExcludeForeignKey | ImportOptions.ExcludeViews
).TableNodes;
#>

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Packages>
    <# foreach (var table in tables) { #>
    <Package Name="Load_<#= table.Schema #>_<#= table.Name #>" ConstraintMode="Linear">
      <Tasks>
        <ExecuteSQL Name="Truncate" ConnectionName="Target">
          <DirectInput>TRUNCATE TABLE stg.<#= table.Name #></DirectInput>
        </ExecuteSQL>
        <Dataflow Name="LoadData">
          <Transformations>
            <OleDbSource Name="Source" ConnectionName="Source">
              <DirectInput>SELECT * FROM [<#= table.Schema #>].[<#= table.Name #>]</DirectInput>
            </OleDbSource>
            <OleDbDestination Name="Target" ConnectionName="Target">
              <ExternalTableOutput Table="stg.<#= table.Name #>" />
            </OleDbDestination>
          </Transformations>
        </Dataflow>
      </Tasks>
    </Package>
    <# } #>
  </Packages>
</Biml>

This single template generates hundreds of packages automatically.

Pattern 2: Error Handling with Event Handlers

Apply consistent error logging across all packages:

<# foreach (var table in RootNode.Tables) { #>
<Package Name="Load_<#= table.Name #>">
  <Tasks>
    <Dataflow Name="LoadData">
      <!-- Data flow content -->
    </Dataflow>
  </Tasks>
  <Events>
    <Event EventType="OnError">
      <Tasks>
        <ExecuteSQL Name="LogError" ConnectionName="Target">
          <DirectInput>
            INSERT INTO dbo.ErrorLog (PackageName, ErrorMessage, ErrorTime)
            VALUES ('<#= table.Name #>', ?, GETDATE())
          </DirectInput>
          <Parameters>
            <Parameter Name="0" VariableName="System.ErrorDescription" DataType="String" />
          </Parameters>
        </ExecuteSQL>
      </Tasks>
    </Event>
  </Events>
</Package>
<# } #>

Pattern 3: Master Workflow Orchestration

Execute child packages with priority grouping:

<#@ template tier="50" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Packages>
    <Package Name="Master_LoadAll" ConstraintMode="Linear">
      <Tasks>
        <!-- High priority tables first (parallel) -->
        <Container Name="HighPriority" ConstraintMode="Parallel">
          <Tasks>
            <# foreach (var pkg in RootNode.Packages.Where(p => p.GetTag("Priority") == "High")) { #>
            <ExecutePackage Name="Exec_<#= pkg.Name #>">
              <Package PackageName="<#= pkg.Name #>" />
            </ExecutePackage>
            <# } #>
          </Tasks>
        </Container>
        <!-- Then normal priority (parallel) -->
        <Container Name="NormalPriority" ConstraintMode="Parallel">
          <Tasks>
            <# foreach (var pkg in RootNode.Packages.Where(p => p.GetTag("Priority") != "High")) { #>
            <ExecutePackage Name="Exec_<#= pkg.Name #>">
              <Package PackageName="<#= pkg.Name #>" />
            </ExecutePackage>
            <# } #>
          </Tasks>
        </Container>
      </Tasks>
    </Package>
  </Packages>
</Biml>

For more patterns including incremental loads and staging layers, see Common Patterns.

---

Variables and Expressions

Variables

SSIS variables store values that can be used across tasks within a package. Define variables at the package level and reference them in tasks.

Defining Variables:

<Package Name="LoadCustomer" ConstraintMode="Linear">
  <Variables>
    <Variable Name="RowCount" DataType="Int32">0</Variable>
    <Variable Name="LoadDate" DataType="DateTime">1/1/2000</Variable>
    <Variable Name="SourceQuery" DataType="String">SELECT * FROM Customer</Variable>
    <Variable Name="IsFullLoad" DataType="Boolean">true</Variable>
  </Variables>
</Package>

Variable Attributes:

Attribute	Purpose	Example
Name	Variable identifier	RowCount
DataType	.NET data type	Int32, String, DateTime, Boolean
EvaluateAsExpression	Enable SSIS expressions	true
Namespace	Variable scope	User (default)

ExecuteSQL with Result Binding:

<ExecuteSQL Name="GetRowCount" ConnectionName="Source" ResultSet="SingleRow">
  <DirectInput>SELECT COUNT(*) AS RowCount FROM Customer</DirectInput>
  <Results>
    <Result Name="0" VariableName="User.RowCount"/>
  </Results>
</ExecuteSQL>

Variable in SQL Statement:

<ExecuteSQL Name="LogCount" ConnectionName="Target">
  <DirectInput>INSERT INTO LoadLog (TableName, RowCount, LoadDate)
    VALUES ('Customer', ?, ?)</DirectInput>
    <Parameters>
      <Parameter Name="0" VariableName="User.RowCount" DataType="Int32"/>
      <Parameter Name="1" VariableName="User.LoadDate" DataType="DateTime"/>
    </Parameters>
  </ExecuteSQL>

Automation Tip: Generate Variables Dynamically

<# foreach (var table in RootNode.Tables) { #>
<Package Name="Load_<#= table.Name #>">
  <Variables>
    <Variable Name="SourceRowCount" DataType="Int32">0</Variable>
    <Variable Name="DestRowCount" DataType="Int32">0</Variable>
  </Variables>
  <!-- Use RowCount component to populate variables -->
</Package>
<# } #>

Expressions

SSIS expressions allow dynamic property values at runtime. Use expressions to:

• Build connection strings dynamically
• Calculate file paths based on dates
• Conditionally modify task behavior

PropertyExpression Element:

For common properties, use the Expressions collection with PropertyName:

<ExecuteSQL Name="DynamicQuery" ConnectionName="Source">
  <DirectInput>SELECT 1</DirectInput>
  <Expressions>
    <Expression PropertyName="SqlStatementSource">@[User::SourceQuery]</Expression>
  </Expressions>
</ExecuteSQL>

Common Expression Properties:

Task Type	Property	Expression Example
ExecuteSQL	SqlStatementSource	@[User::Query]
FileSystem	SourcePath	@[User::FilePath] + "\\data.csv"
FlatFileConnection	ConnectionString	@[User::FilePath] + "\\export_" + (DT_STR,8,1252)GETDATE()
Package	Name	"Load_" + @[User::TableName]

Dynamic Query Example:

<ExecuteSQL Name="IncrementalLoad" ConnectionName="Target">
  <DirectInput>SELECT 1</DirectInput>
  <Expressions>
    <Expression PropertyName="SqlStatementSource">
      "SELECT * FROM dbo.Customer WHERE ModifiedDate > '" + (DT_WSTR, 23) @[User::LastLoadDate] + "'"
    </Expression>
  </Expressions>
</ExecuteSQL>

External Property Syntax:

For properties not directly exposed in Biml, use the external property path:

<Package Name="DynamicPackage">
  <Connections>
    <Connection ConnectionName="Target">
      <Expressions>
        <Expression ExternalProperty="ServerName">@[User::TargetServer]</Expression>
        <Expression ExternalProperty="InitialCatalog">@[User::TargetDatabase]</Expression>
      </Expressions>
    </Connection>
  </Connections>
</Package>

Tip: Use expressions for environment-specific values (server names, file paths, credentials) that change between development and production.

---

Event Handlers

Run tasks in response to package events (OnError, OnPreExecute, OnPostExecute):

<Package Name="Package1">
  <Events>
    <Event EventType="OnError">
      <Tasks>
        <ExecuteSQL Name="LogError" ConnectionName="Target">
          <DirectInput>EXEC dbo.uspLogError</DirectInput>
        </ExecuteSQL>
      </Tasks>
    </Event>
    <Event EventType="OnPreExecute">
      <Tasks>
        <ExecuteSQL Name="LogStart" ConnectionName="Target">
          <DirectInput>EXEC dbo.uspLogStart</DirectInput>
        </ExecuteSQL>
      </Tasks>
    </Event>
  </Events>
</Package>

---

Connections

Define connections at the root level for reuse across packages:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <OleDbConnection Name="Source"
      ConnectionString="Provider=SQLNCLI11;Server=SourceServer;Initial Catalog=SourceDB;Integrated Security=SSPI;" />
    <OleDbConnection Name="Target"
      ConnectionString="Provider=SQLNCLI11;Server=TargetServer;Initial Catalog=TargetDB;Integrated Security=SSPI;" />
  </Connections>
</Biml>

Override connection properties per package with expressions:

<Package Name="Package1">
  <Connections>
    <Connection ConnectionName="Target">
      <Expressions>
        <Expression ExternalProperty="ConnectionString">@[User::TargetConnectionString]</Expression>
      </Expressions>
    </Connection>
  </Connections>
</Package>

---

Script Projects

Define reusable script tasks centrally, reference from multiple packages:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <ScriptProjects>
    <ScriptTaskProject Name="LoggingScript" ProjectCoreName="ST_Logging">
      <AssemblyReferences>
        <AssemblyReference AssemblyPath="Microsoft.SqlServer.ManagedDTS.dll" />
        <AssemblyReference AssemblyPath="System.dll" />
      </AssemblyReferences>
      <Files>
        <File Path="ScriptMain.cs">
public partial class ScriptMain : VSTARTScriptObjectModelBase
{
  public void Main()
  {
    // Custom logging logic here
    Dts.TaskResult = (int)DTSExecResult.Success;
  }
}
        </File>
      </Files>
    </ScriptTaskProject>
  </ScriptProjects>
  <Packages>
    <Package Name="Package1">
      <Tasks>
        <Script Name="RunLogging">
          <ScriptTaskProjectReference ScriptTaskProjectName="LoggingScript" />
        </Script>
      </Tasks>
    </Package>
  </Packages>
</Biml>

Change the script once, rebuild all packages that reference it.

---

Project Deployment Model

For SQL Server 2012+, use project deployment model for shared connections and parameters.

Project-Level Connections

Add CreateInProject="true" for shared connections:

<Connections>
  <OleDbConnection Name="SharedConnection" CreateInProject="true"
    ConnectionString="Provider=SQLNCLI11;Server=localhost;Initial Catalog=DB;Integrated Security=SSPI;" />
</Connections>

Package Projects

Define project structure with parameters:

<Projects>
  <PackageProject Name="ETLProject">
    <Connections>
      <Connection ConnectionName="SharedConnection" />
    </Connections>
    <Packages>
      <Package PackageName="Package1" IsEntryPoint="true" />
      <Package PackageName="Package2" />
    </Packages>
    <Parameters>
      <Parameter Name="Environment" DataType="String">DEV</Parameter>
    </Parameters>
  </PackageProject>
</Projects>

---

File Formats

Define flat file formats centrally for reuse:

<FileFormats>
  <FlatFileFormat Name="CustomerFormat" FlatFileType="Delimited" RowDelimiter="CRLF">
    <Columns>
      <Column Name="CustomerID" Delimiter="Comma" />
      <Column Name="Name" DataType="String" Length="100" Delimiter="Comma" />
      <Column Name="Email" DataType="String" Length="256" Delimiter="CRLF" />
    </Columns>
  </FlatFileFormat>
</FileFormats>
<Connections>
  <FlatFileConnection Name="CustomerFile" FileFormat="CustomerFormat" FilePath="C:\Data\Customer.csv" />
</Connections>

---

CI/CD Integration

Version Control Best Practices

Organize Biml files by tier:

/biml
  /environment          # Tier 10: Connections, databases
  /metadata             # Tier 20: Table imports
  /patterns             # Tier 30-40: Package generation
  /output               # Generated packages (add to .gitignore)

Commit:

• Biml/BimlScript source files
• Configuration files

Exclude (.gitignore):

• Generated .dtsx packages
• Build output folders

Command-Line Build

# BimlStudio command line
& "C:\Program Files\Varigence\BimlStudio\Biml.exe" `
  /build /project:MyProject.mst /output:.\output

Azure DevOps Pipeline

trigger:
 - main

pool:
 vmImage: 'windows-latest'

steps:
- task: PowerShell@2
 displayName: 'Build Biml'
 inputs:
  targetType: 'inline'
  script: |
   & "C:\Program Files\Varigence\BimlStudio\Biml.exe" `
    /build /project:MyProject.mst /output:$(Build.ArtifactStagingDirectory)

- task: PublishBuildArtifacts@1
 inputs:
  PathtoPublish: '$(Build.ArtifactStagingDirectory)'
  ArtifactName: 'SSISPackages'

See Automation and Patterns for detailed CI/CD guidance.

---

Beyond SSIS: Modern Platforms

The patterns you learn for SSIS apply conceptually to cloud platforms. BimlFlex extends these capabilities:

SSIS Concept	ADF	Databricks	Snowflake	Fabric
Package	Pipeline	Job/Workflow	Task	Pipeline
Data Flow	Copy Activity	Notebook	COPY INTO	Dataflow
Connection	Linked Service	Cluster Config	Stage	Connection
Variable	Parameter	Widget	Variable	Parameter

Platform documentation:

• Azure Data Factory
• Databricks
• Snowflake
• Microsoft Fabric

---

Legacy Features

Package Configurations (Pre-2012)

For SQL Server 2008 and earlier, or package deployment model. Prefer project parameters for SQL Server 2012+.

<Package Name="Package1" AutoCreateConfigurationsType="Xml">
  <!-- Creates .dtsConfig file for connection overrides -->
</Package>

See Package Configuration Reference for details.

Built-in Logging

SSIS Catalog logging (SQL Server 2012+) is preferred over built-in log providers. For legacy scenarios:

<Package Name="Package1">
  <LogProviders>
    <TextLogProvider Name="TextLog" ConnectionName="LogFile" />
  </LogProviders>
  <LogEvents>
    <LogEvent EventName="OnError">
      <EventColumns>
        <EventColumn>Computer</EventColumn>
        <EventColumn>SourceName</EventColumn>
      </EventColumns>
    </LogEvent>
  </LogEvents>
</Package>

---

Conclusion

Biml provides full fidelity with SSIS - if you can build it in SSIS, you can generate it with Biml. Combined with BimlScript automation, you can:

• Reduce development time from weeks to hours
• Eliminate manual errors with consistent patterns
• Respond to changes quickly by regenerating from metadata
• Scale to enterprise workloads with hundreds of packages

Next Steps

1. Common Patterns - Ready-to-use staging, incremental load, and error handling patterns
2. Build Automated Staging Layer - Complete end-to-end tutorial
3. Automation and Patterns - Metadata-driven development concepts
4. Troubleshooting Guide - Debug compilation errors
5. BimlFlex - Enterprise platform with UI-driven metadata management