Don’t Repeat Your Biml – CallBimlScript

Biml (Business Intelligence Markup Language) - CallBimlScriptAre 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:

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:

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 :)

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.

Who is Cathrine Wilhelmsen?

Cathrine is a Microsoft Data Platform MVP, BimlHero, author, speaker, blogger and chronic volunteer who loves teaching and sharing knowledge. She works as a consultant, technical architect and developer, focusing on Data Warehouse and Business Intelligence projects. She loves sci-fi, chocolate, craft beers, ciders, cat gifs and smilies :)

11 thoughts on “Don’t Repeat Your Biml – CallBimlScript”

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.io/image/z1na513tt/

Thank You very much for your answer.

Share Your Thoughts?