Biml, Microsoft Technologies, SSIS

BimlScript – Get to Know Your Code Nuggets

gold nuggetsIn BimlScript, we embed nuggets of C# or VB code into our Biml (XML) in order to replace variables and automate the creation of our BI artifacts (databases, tables, SSIS packages, SSAS cubes, etc.). Code nuggets are a major ingredient in the magic sauce that is meta-data driven SSIS development using BimlScript.

There are 5 different types of code nuggets in BimlScript:

  • Text nuggets
  • Control nuggets
  • Class nuggets
  • Comments
  • Directives

Over the next several posts I’ll cover each type of code nugget and provide examples of their use.

Text Nuggets

Text nuggets evaluate the expression they contain and then replace the text nugget with the string representation of the value of the expression. I use them often to switch out names of packages, tasks, and components as well as source and destination tables in SSIS development when creating packages based upon a design pattern.

Text nuggets start with <#= and end with #>. Notice there is an equals sign at the beginning of the text nugget but not at the end.

Text nuggets are very useful. You can include complex business logic in the expressions. And the expression result can be any data type. The BimlScript compiler will automatically convert it to text before replacing the code nugget with the result. Like all code nuggets, text nuggets can be a single line or multiple lines.

Let’s take a look at some examples.

Note: The Biml script below has variables, connections, and a package all in one file for ease of demonstration. Normally I would split these out into separate files. 

<#* 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>

This BimlScript creates an SSIS package that truncates and reloads data in a single table.

Notice that you can use the text nuggets alone, together, or with literal text.

The <#=PackageName#> text nugget simply replaces the value for the name of the package with the current value of the PackageName variable I set at the top of the file.

The DataFlow Task name is a combination of text and a code nugget.

<Dataflow Name="DFT Stage_<#=DestinationTable#>">

This comes in handy if you would like to enforce certain naming standards. My personal preference is to use naming conventions suggested by Jamie Thomson, so I build them into my Biml and then use text nuggets as shown above to change the rest of the name to reflect the nature of the current task.

The truncate table statement in the Execute SQL Task contains text and two code nuggets that reference a variable and use a function to convert the variable value into all uppercase text:

Truncate Table [<#=DestinationSchema.ToUpper()#>].[<#=DestinationTable.ToUpper()#>];

Hopefully you can see how text nuggets are useful when creating multiple packages at the same time. They allow you to switch out the values that change for each package and create consistent naming standards.

To get to know other types of code nuggets, see Get To Know Your Control Nuggets.

2 thoughts on “BimlScript – Get to Know Your Code 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 )

Facebook photo

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

Connecting to %s