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:

Caller.biml:

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

Callee.biml:

<#@ 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="http://schemas.varigence.com/biml.xsd">
  <Connections>	
    <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;" />
  </Connections>

  <Packages>
    <# foreach (var table in sourceMetadata.TableNodes) { #>
      <#=CallBimlScript("Callee.biml", table, "Source", "Destination", "stg") #>
    <# } #>
  </Packages>
</Biml>
<#@ 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">
  <Variables>
    <Variable Name="NewRows" DataType="Int32">0</Variable>
  </Variables>
  <Tasks>
    <ExecuteSQL Name="SQL Truncate <#=Table.Schema#> <#=Table.Name#>" ConnectionName="<#=DestinationConnection#>">
      <DirectInput>TRUNCATE TABLE <#=DestinationSchema#>.<#=Table.Schema#>_<#=Table.Name#></DirectInput>
    </ExecuteSQL>				
    <Dataflow Name="Load <#=Table.Schema#> <#=Table.Name#>">
      <Transformations>
        <OleDbSource Name="Source <#=Table.Schema#> <#=Table.Name#>" ConnectionName="<#=SourceConnection#>">
          <ExternalTableInput Table="<#=Table.SchemaQualifiedName#>" />
        </OleDbSource>
        <DerivedColumns Name="Add LoadDate">
          <Columns>
            <Column Name="LoadDate" DataType="DateTime">@[System::StartTime]</Column>
          </Columns>
        </DerivedColumns>
        <RowCount Name="Count NewRows" VariableName="User.NewRows" />
        <OleDbDestination Name="Destination <#=Table.Schema#> <#=Table.Name#>" ConnectionName="<#=DestinationConnection#>">
          <ExternalTableOutput Table="[<#=DestinationSchema#>].[<#=Table.Schema#>_<#=Table.Name#>]" />
        </OleDbDestination>
      </Transformations>
    </Dataflow>
  </Tasks>
</Package>

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" #>
<LogEvents>
	
	<# 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>
	<# } #>

</LogEvents>

Summary

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.

About the Author

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

Comments

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. Be kind to each other. Thanks!

Pingback: BIML. Повторное использование кода. | Korshikov's

Hi Catherine, I have been followed your post for a while. Your post are GREAT resource in my biml learning journey. Thanks for your sharing.
In your Caller.biml (in the example of create package), I want to pass the database name in the connection String via a parameter, instead of hard Code. The value of pamameter will be given by environment variable when run the package via SQL agent. Do you have ways to do that?

Hi Beverly, thank you! It sounds like you are mixing package generation and package execution. CallBimlScript will only be called when you generate the SSIS packages, not when the generated packages are executed.

Hey Cathrine, after seeing your session on the SQL Server Days in Belgium this september I got to work with BIML at my workplace. Most of it works great and is self explanatory, but I have an issue calling bimlscript with parameters. I would like to use, as a parameter, an object of a custom class. So first I reference my class (using an include file) in my caller, but I can’t use the object in the callee. Either because it does not know the class (when I do not include the code file in the callee) or because it tries to cast the object from one instance of the class to another instance of the class (when i do include the code file in the callee).
Do you have an idea how I could fix this? So far I have worked around it using includes instead of CallBimlScript, but I’m not entirely happy with the separation of concerns, as my caller needs to make sure that it has the right variables filled the right way every time, rather than just pass on the needed object.

Hi Tom, have you tried using code files instead of wrapping your code in class nuggets? Or possibly adding your object to an ObjectTag? (“ObjectTag is essentially a .NET Dictionary object that enables storage of .NET objects in a similar way to annotations in Biml.”)

Thanks for your response Cathrine, using a C# code library did indeed fix the issue. Haven’t tested the ObjectTag yet, will have to give that a go as well :)

Hi Cathrine,

Thanks for you for Sharing your knowledge on BIML. Here i have one question,

If i have BIML file in my file local drive , how i need to execute outside of BIDS(instead of right click on BIML in BIDS to automate from Application) to generate SSIS packages to local drive

Thanks,
Kiran A

Hi Kiran, you will have to buy a license of Mist to automate SSIS package generation.

Thanks Cathrine for your quick response

Pingback: Start a system process in Biml with C# – Frysdiskens Code Chop Shop Store

Hi Cathrine,

I am a BIML novice and I find your blogs very, very helpful. I have a question. How do you access SSIS package variable in BIML Control Nugget? Is this even possible?

I’ve attached picture showing my problem (I hope it will work):
[![Biml.png])

In case above link doesnt work I am attaching direct link: https://postimg.cc/CzvW5CHm

Thank You very much for your answer.

Hi Catherine,

Thanks for that great post!

I’m running into what I think might be a bug. I usually code my BIML Script in C# but I am now at a client where the standard is VB.NET (yeah… I know…) and I am trying to use CallBimlScript to get each package definition for each table. All packages are the same except for a given type of table where the derived column task should have 3 more columns.

writting the If…Then statement in VB.NET in the called file results in absolutely nothing being returned from it. Not just what’sin the if statement, but nothing at all.

If I write it in C# (even though my caller specifies “Language=VB”) works and I get the desired BIML out of by callee. have you ever seen such behavior ?

Hi David, I have absolutely no experience working with VB.NET in Biml, so I recommend reaching out to Ben Weissman (@bweissman). He’s the VB.NET hero of the BimlHeroes :D

Hi! This is Cathrine (again). Just a reminder. 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. Be kind to each other. Thanks!

Share Your Thoughts?