Metadata Design Patterns in Biml
Why Patterns Matter
Building a single SSIS package is straightforward. Building and maintaining a consistent design pattern across every package in a data warehouse project is harder. Biml addresses that gap by letting templates and patterns be reused across many ETL tasks rather than copied by hand. The same metadata that drives one package can drive a hundred, and the pattern stays consistent across the set.
This walkthrough builds a staging package that loads a flat currency file into a SQL Server staging table, then refactors the package into a metadata driven generator.
Step One: A Minimal Biml Package
The simplest useful Biml file declares a Connections block and a Packages block with one empty package.
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Connections>
<Connection Name="StagingOleDb"
ConnectionString="Data Source=.\SQL2019;Initial Catalog=DemoStaging;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" />
</Connections>
<Packages>
<Package Name="LoadStagingDemo" ConstraintMode="Linear">
<Tasks>
<ExecuteSQL Name="Run Sample Query" ConnectionName="StagingOleDb">
<DirectInput>SELECT TOP 10 * FROM sys.tables</DirectInput>
</ExecuteSQL>
</Tasks>
</Package>
</Packages>
</Biml>
Right click the file, choose Check Biml for Errors to validate, then Generate SSIS Packages to produce a .dtsx that runs in Visual Studio.
Step Two: A Flat File Source
Add a flat file connection along with its file format definition. The file format describes column delimiters, code page, and unicode handling separately from the connection that points at the file path.
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<FileFormats>
<FlatFileFormat Name="CurrencyCsvFormat"
RowDelimiter="LF"
ColumnNamesInFirstDataRow="true"
IsUnicode="false">
<Columns>
<Column Name="IsoCode" DataType="String" Length="3" Delimiter="Comma" CodePage="1252" />
<Column Name="DisplayName" DataType="String" Length="200" Delimiter="LF" />
</Columns>
</FlatFileFormat>
</FileFormats>
<Connections>
<Connection Name="StagingOleDb"
ConnectionString="Data Source=.\SQL2019;Initial Catalog=DemoStaging;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" />
<FlatFileConnection Name="CurrencyCsvConn"
FileFormat="CurrencyCsvFormat"
FilePath="C:\imports\currency.csv" />
</Connections>
<Packages>
<Package Name="LoadStagingDemo" ConstraintMode="Linear">
<Tasks>
<ExecuteSQL Name="Truncate Currency" ConnectionName="StagingOleDb">
<DirectInput>TRUNCATE TABLE dbo.Currency</DirectInput>
</ExecuteSQL>
<Dataflow Name="DFT Load Currency">
<Transformations>
<FlatFileSource Name="FF Source" ConnectionName="CurrencyCsvConn" />
</Transformations>
</Dataflow>
</Tasks>
</Package>
</Packages>
</Biml>
Step Three: A Touch of BimlScript
Hardcoded names limit reuse. Lift the table name into a BimlScript variable so the same template can target other tables with one edit.
<# var stagingTable = "dbo.Currency"; #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Connections>
<Connection Name="StagingOleDb"
ConnectionString="Data Source=.\SQL2019;Initial Catalog=DemoStaging;Provider=SQLNCLI11.1;Integrated Security=SSPI;" />
</Connections>
<Packages>
<Package Name="LoadStagingDemo" ConstraintMode="Linear">
<Tasks>
<ExecuteSQL Name="Truncate Staging" ConnectionName="StagingOleDb">
<DirectInput>TRUNCATE TABLE <#= stagingTable #></DirectInput>
</ExecuteSQL>
</Tasks>
</Package>
</Packages>
</Biml>
After expansion the DirectInput holds TRUNCATE TABLE dbo.Currency. The same approach can replace any hardcoded value with a metadata driven one.
Step Four: Drive Everything from a Database
Move the file format and column definitions into two metadata tables.
CREATE TABLE dbo.FlatFileMaster (
Name nvarchar(50) NOT NULL,
CodePage nvarchar(50) NULL,
RowDelimiter nvarchar(50) NULL,
ColumnNamesInFirstDataRow nvarchar(50) NULL,
IsUnicode nvarchar(50) NULL,
FlatFileType nvarchar(50) NULL,
TextQualifier nvarchar(50) NULL
);
CREATE TABLE dbo.FlatFileColumnDetails (
FlatFileName nvarchar(50) NULL,
ColumnName nvarchar(50) NULL,
Length nchar(10) NULL,
InputLength nchar(10) NULL,
MaximumWidth nchar(10) NULL,
DataType nchar(10) NULL,
ColumnType nchar(10) NULL,
CodePage nchar(10) NULL,
Delimiter nchar(10) NULL,
TextQualified nchar(10) NULL
);
Populate one row in FlatFileMaster for the currency file and one row per column in FlatFileColumnDetails. The generator then reads both tables and emits the equivalent FlatFileFormat block automatically.
<#@ import namespace="System.Data" #>
<#
string metaConnString = "Data Source=.\\SQL2019;Initial Catalog=DemoStaging;Provider=SQLNCLI11.1;Integrated Security=SSPI;";
string headerSql = "SELECT Name, CodePage, RowDelimiter, ColumnNamesInFirstDataRow, IsUnicode, FlatFileType, TextQualifier FROM dbo.FlatFileMaster";
string columnSql = "SELECT FlatFileName, ColumnName, Length, InputLength, MaximumWidth, DataType, ColumnType, CodePage, Delimiter, TextQualified FROM dbo.FlatFileColumnDetails ORDER BY ColumnName";
#>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Connections>
<OleDbConnection Name="StagingOleDb"
ConnectionString="<#= metaConnString #>" />
<FlatFileConnection Name="CurrencyCsvConn"
FileFormat="CurrencyCsvFormat"
FilePath="C:\imports\currency.csv"
CreateInProject="false" />
</Connections>
<FileFormats>
<#
DataTable files = ExternalDataAccess.GetDataTable(metaConnString, headerSql);
foreach (DataRow file in files.Rows) {
#>
<FlatFileFormat Name="<#= file["Name"] #>"
CodePage="<#= file["CodePage"] #>"
RowDelimiter="<#= file["RowDelimiter"] #>"
ColumnNamesInFirstDataRow="<#= file["ColumnNamesInFirstDataRow"] #>"
IsUnicode="<#= file["IsUnicode"] #>"
FlatFileType="<#= file["FlatFileType"] #>">
<Columns>
<#
DataTable cols = ExternalDataAccess.GetDataTable(metaConnString, columnSql);
foreach (DataRow col in cols.Rows) {
#>
<Column Name="<#= col["ColumnName"] #>"
Length="<#= col["Length"] #>"
InputLength="<#= col["InputLength"] #>"
MaximumWidth="<#= col["MaximumWidth"] #>"
DataType="<#= col["DataType"] #>"
ColumnType="<#= col["ColumnType"] #>"
CodePage="<#= col["CodePage"] #>"
Delimiter="<#= col["Delimiter"] #>"
TextQualified="<#= col["TextQualified"] #>" />
<# } #>
</Columns>
</FlatFileFormat>
<# } #>
</FileFormats>
<Packages>
<Package ConstraintMode="Linear" Name="LoadStagingDemo">
<Variables>
<Variable DataType="Int32" Name="LoadedRows">0</Variable>
</Variables>
<Tasks>
<Dataflow Name="DFT Load Currency">
<Transformations>
<FlatFileSource Name="FF Source" ConnectionName="CurrencyCsvConn" />
<RowCount Name="Count Rows" VariableName="User.LoadedRows" />
<OleDbDestination Name="OleDb Destination" ConnectionName="StagingOleDb">
<ExternalTableOutput Table="Currency" />
</OleDbDestination>
</Transformations>
</Dataflow>
</Tasks>
</Package>
</Packages>
</Biml>
The two queries fan out: one row in FlatFileMaster produces one FlatFileFormat element, and the matching rows in FlatFileColumnDetails produce its Column children. Adding a second file is a metadata insert plus a regenerate, not a code change.
Why This Pattern Wins
Once metadata drives the generation, every change is centralized. Adding columns, switching code pages, or onboarding new files becomes data entry rather than file editing. The generated packages stay consistent because they all flow through the same template.
The same approach extends naturally: precedence constraints between tasks, constraint mode choices on the package level, and reusable transformations all fit into the metadata model the same way the file format and columns did.