Skip to content

Don’t Repeat Your Biml – CallBimlScript

Biml (Business Intelligence Markup Language) - CallBimlScript

Are you using Biml so you won’t have to do the same tasks over and over and over again in multiple SSIS packages? If so, you probably don’t want to write the same Biml code over and over and over again either. Instead, you can move common code to separate files, centralize and reuse these files in many projects, and update code in one file to make changes to all projects. One of the ways to apply this Don’t Repeat Yourself software engineering principle in Biml is to use CallBimlScript.

In addition to using CallBimlScript, there are four other main ways you can avoid repeating your Biml code:

In this post we will look at how to use CallBimlScript with Parameters.

CallBimlScript with Parameters

If you come from the world of SQL, you will see that using CallBimlScript is similar to using a stored procedure. If you come from the world of programming, you will see that CallBimlScript is similar to a method or function, but with some differences.

The Biml file with CallBimlScript (the Caller) passes parameters to another Biml file (the Callee). The Callee can then use these input parameters as regular variables and to control logic. The order you specify the parameters in CallBimlScript in the Caller must match the order you have specified the parameter properties in the Callee. Below is a very simple example of a caller and a callee:


  <#=CallBimlScript("Callee.biml", StringParameter, IntParameter)#>


<#@ property name="StringParameter" type="String" #>
<#@ property name="IntParameter" type="Int32" #>
<Package Name="<#=StringParameter#>_<#=IntParameter#>" />

You use an Expression Control Block (that starts with the equal sign <#= … #>) when you want to use CallBimlScript. Expression Control Blocks first parse the content inside the block as a string, and then the Expression Control Block is replaced by that string. It is important that the callee only contains the code that can replace the Expression Control Block without causing invalid syntax errors. In the example above, CallBimlScript is used inside <Packages></Packages>, so the callee must only contain <Package> elements.

One of my favorite things about CallBimlScript is that you are not limited to using simple parameter types like String, Int32 or Boolean. You can pass all kinds of parameters! Just think about the cool solutions you can create by passing parameters such as AstTableNode, List<> or DataTable :)

In BimlExpress, you only need to right-click on Caller.biml and click Generate SSIS Packages, and it will locate and use Callee.biml. If the callee is located outside your project, you will have to use an appropriate file path when using CallBimlScript.

How does CallBimlScript work?

1. The Caller has a line of CallBimlScript code. In this example, it has a blue parameter:

CallBimlScript - Caller

2. The Caller (left) passes the blue parameter to the Callee (right):

CallBimlScript - Pass Parameters

3. The Callee might execute some logic based on the parameter, such as an if / else statement:

CallBimlScript - Execute Logic

4. The Callee returns the evaluated code to the Caller. In this example, the blue parameter only returns the blue lines of code from the Callee:

CallBimlScript - Return Code

5. The line of CallBimlScript code in the Caller is replaced with the blue lines of code from the Callee:

CallBimlScript - Replace CallBimlScript with Code

Example: CallBimlScript to create Packages

Below is an extended example where I have created a callee that creates a Truncate / Load package. It accepts four parameters: AstTableNode, source connection, destination connection and destination schema. I can reuse this callee in all my projects where I Truncate / Load tables, without having to copy and paste the actual code. If I want to change the pattern, for example if I want to add a second derived column, I will only have to change it in that one callee Biml file. The CallBimlScript specific code lines are highlighted:

<# var sourceConnection = SchemaManager.CreateConnectionNode("Source", "Data Source=.;Initial Catalog=SourceDatabase;Provider=SQLNCLI11.1;Integrated Security=SSPI;"); #>
<# var sourceMetadata = sourceConnection.GetDatabaseSchema(ImportOptions.ExcludeViews); #>

<Biml xmlns="">
    <OleDbConnection Name="Source" ConnectionString="Data Source=.;Initial Catalog=SourceDatabase;Provider=SQLNCLI11.1;Integrated Security=SSPI;" />
    <OleDbConnection Name="Destination" ConnectionString="Data Source=.;Initial Catalog=DestinationDatabase;Provider=SQLNCLI11.1;Integrated Security=SSPI;" />

    <# foreach (var table in sourceMetadata.TableNodes) { #>
      <#=CallBimlScript("Callee.biml", table, "Source", "Destination", "stg") #>
    <# } #>
<#@ property name="Table" type="AstTableNode" #>
<#@ property name="SourceConnection" type="String" #>
<#@ property name="DestinationConnection" type="String" #>
<#@ property name="DestinationSchema" type="String" #>

<Package Name="Load_<#=Table.Schema.Name#>_<#=Table.Name#>" ConstraintMode="Linear">
    <Variable Name="NewRows" DataType="Int32">0</Variable>
    <ExecuteSQL Name="SQL Truncate <#=Table.Schema#> <#=Table.Name#>" ConnectionName="<#=DestinationConnection#>">
      <DirectInput>TRUNCATE TABLE <#=DestinationSchema#>.<#=Table.Schema#>_<#=Table.Name#></DirectInput>
    <Dataflow Name="Load <#=Table.Schema#> <#=Table.Name#>">
        <OleDbSource Name="Source <#=Table.Schema#> <#=Table.Name#>" ConnectionName="<#=SourceConnection#>">
          <ExternalTableInput Table="<#=Table.SchemaQualifiedName#>" />
        <DerivedColumns Name="Add LoadDate">
            <Column Name="LoadDate" DataType="DateTime">@[System::StartTime]</Column>
        <RowCount Name="Count NewRows" VariableName="User.NewRows" />
        <OleDbDestination Name="Destination <#=Table.Schema#> <#=Table.Name#>" ConnectionName="<#=DestinationConnection#>">
          <ExternalTableOutput Table="[<#=DestinationSchema#>].[<#=Table.Schema#>_<#=Table.Name#>]" />

Example: CallBimlScript to control SSIS Logging

Below is an extended example where I have created a callee that accepts a single parameter called LogType. Based on the value of the LogType parameter, the SSIS package will log some or all events. This logging code is particularly verbose (I have shortened it in this example) and I absolutely do not want to copy, paste and maintain it in multiple files. With CallBimlScript, it is very easy to change! I can just change the value of the LogType parameter from “LogErrors” to “LogAll”, regenerate my SSIS packages, and all the logging will be updated :)

<#=CallBimlScript("CalleeLogging.biml", "LogErrors")#>
<#=CallBimlScript("CalleeLogging.biml", "LogAll")#>
<#@ property name="LogType" type="String" #>
	<# if (LogType == "LogErrors" || LogType == "LogAll") { #>
		<LogEvent EventName="OnWarning">...</LogEvent>
		<LogEvent EventName="OnError">...</LogEvent>
		<LogEvent EventName="OnTaskFailed">...</LogEvent>
	<# } #>
	<# if (LogType == "LogDiagnostic" || LogType == "LogAll") { #>
		<LogEvent EventName="Diagnostic">...</LogEvent>
		<LogEvent EventName="DiagnosticEx">...</LogEvent>
	<# } #>
	<# if (LogType == "LogAll") { #>
		<LogEvent EventName="OnInformation">...</LogEvent>
		<LogEvent EventName="OnExecStatusChanged">...</LogEvent>
		<LogEvent EventName="OnVariableValueChanged">...</LogEvent>
		<LogEvent EventName="OnPreValidate">...</LogEvent>
		<LogEvent EventName="OnPostValidate">...</LogEvent>
		<LogEvent EventName="OnPreExecute">...</LogEvent>
		<LogEvent EventName="OnPostExecute">...</LogEvent>
		<LogEvent EventName="OnProgress">...</LogEvent>
		<LogEvent EventName="OnQueryCancel">...</LogEvent>
	<# } #>



CallBimlScript works like a parameterized include file, or like a SQL Server stored procedure. The file to be called (“callee”) specifies accepted parameters: <#@ property name=”Parameter” type=”String” #>. The file that calls (“caller”) passes parameters: <#=CallBimlScript(“Callee.biml”, Parameter #>. The CallBimlScript block in the caller will be replaced by the returned contents of the callee file.

If you only need to include code without using parameters or controlling logic, you can use Include files instead.

Who is Cathrine Wilhelmsen?

Cathrine is a Microsoft Data Platform MVP, BimlHero, Microsoft Certified Solutions Expert, author, speaker, blogger and chronic volunteer who loves teaching and sharing knowledge. She works as a Senior Business Intelligence Consultant in Inmeta, focusing on Azure and the Microsoft Data Platform. She loves sci-fi, chocolate, coffee, craft beers, ciders, cat gifs and smilies :)


Hi! This is Cathrine. Thank you so much for visiting my blog. I'd love to hear your thoughts, but please keep in mind that I'm not technical support for any products mentioned in this post :) Off-topic questions, comments and discussions may be moderated. Thanks!

Click to Show / Hide Comments