Biml, Microsoft Technologies, SSIS

BimlScript – Get to Know Your Control Nuggets

This is post #2 of my BimlScript – Get to Know Your Code Nuggets series. To learn about text nuggets, see my first post.  

The next type of BimlScript code nugget I’d like to discuss is the control nugget. Control nuggets allow you to insert control logic to determine what Biml is generated and used to create your BI artifacts (packages, cubes, etc.).  Control nuggets can be used to:

  • Add conditional logic so that specified Biml fragments are only generated when certain criteria are met
  • Add loops to repeat Biml fragments multiple times
  • Define variables that will be used later
  • Access external data from databases, flat files or other sources that will be used in the generation of the final Biml code

Control nuggets start with <# and end with #>. Just like text nuggets, control nuggets can be a single line or multiple lines. And they can contain simple or complex logic.

There were actually control nuggets in the text nugget example from the previous post. The variable declarations at the top of the file (lines 4 – 10) are control nuggets.

<#* The variables below are control nuggets.
They will be discussed later but are needed for ths example.*#>
var PackageName = "Stage_AW_SalesReason";
var SourceTable = "SalesReason";
var SourceSchema = "Sales";
var DestinationTable = "SalesReason";
var DestinationSchema = "Staging";
<Biml xmlns=""&gt;
<OleDbConnection Name="AW2014"
ConnectionString = "Data Source=.\SQL2014;Initial Catalog=AdventureWorks2014;Integrated Security=SSPI;Provider=SQLNCLI11.1;"/>
<OleDbConnection Name="MyDataMart"
ConnectionString = "Data Source=.\SQL2014;Initial Catalog=AWBIML;Integrated Security=SSPI;Provider=SQLNCLI11.1;"/>
<Package Name="<#=PackageName#>" ConstraintMode="Linear" ProtectionLevel="DontSaveSensitive">
<ExecuteSQL Name="SQL Truncate_<#=DestinationTable#>" ConnectionName="AW2014">
<DirectInput>Truncate Table [<#=DestinationSchema.ToUpper()#>].[<#=DestinationTable.ToUpper()#>];</DirectInput>
<Dataflow Name="DFT Stage_<#=DestinationTable#>">
<OleDbSource Name="OLE_SRC <#=SourceTable#>" ConnectionName="AW2014" >
<DirectInput>SELECT * FROM [<#=SourceSchema.ToUpper()#>].[<#=SourceTable.ToUpper()#>];
<OleDbDestination Name="OLE_SRC <#=DestinationSchema#><#=DestinationTable#>" ConnectionName="MyDataMart">
<ExternalTableOutput Table="<#=DestinationSchema#>.<#=DestinationTable#>"></ExternalTableOutput>

Below is another example file that uses code nuggets. I like to separate my design patterns. project connections, and package generation into separate files. The BimlScript shown below is from a slightly altered “caller file”, the file that I would execute to create the SSIS packages that should follow a specified design pattern. This caller file is generating Type 1 slowly changing dimensions.

<#*This file retrieves variable values from a database
and passes them to another Biml file file that contains
the design pattern for a type 1 SCD*#>
<#*The items directly below this comment are directives*#>
<#@ template language="C#" hostspecific="true" #>
<#@ import namespace="System.Data" #>
<#@ import namespace="System.Data.SqlClient" #>
<#@ import namespace="System.IO" #>
<Biml xmlns=""&gt;
string AuditConnectionString = "Data Source=.\\SQL2014;Initial Catalog=AWBIML;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;";
string SrcTableQuery = @"
FROM [BIML].[DimensionPackageVariables]
where PackageType ='Dim1'
DataTable dt = null;
dt = ExternalDataAccess.GetDataTable(AuditConnectionString, SrcTableQuery);
<#@ include file="ProjectConnections.biml" #>
<# foreach (DataRow dr in dt.Rows) { #>
<#=CallBimlScript("Dim1.biml", dr[0].ToString(), dr[1].ToString(), dr[2].ToString(), dr[3].ToString(), dr[4].ToString(), dr[5].ToString(), dr[6].ToString(), dr[7].ToString(), dr[8].ToString(), dr[9].ToString(), dr[10].ToString(), dr[11].ToString(), dr[12].ToString())#>
<# } #>

view raw


hosted with ❤ by GitHub

In my Biml framework, I store the data I need in order to generate my packages in SQL tables. You can see the control nugget in lines 14 – 40 retrieving data from my database and storing it in a data table for later use.

On line 46, there is a control nugget containing a for each loop. For each row in the data table, it calls a BimlScript file that creates a package that uses my Type 1 SCD design pattern with the variables from data table. Notice that the end curly brace for my loop is in a separate control nugget.

There are many different ways to use control nuggets that aren’t covered here.  From these two examples you can start to see how I might combine text nuggets and control nuggets to automate my SSIS package creation and employ consistent design patterns. But there are still a few missing pieces that will be filled in when I cover the remaining code nugget types.

2 thoughts on “BimlScript – Get to Know Your Control Nuggets”

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s