Photo by Evgeni Tcherkasski on Unsplash

I have started my career in Business Intelligence with SSIS. We had a reasonably small data warehouse, and my job was to maintain the existing SSIS packages and create new ones. Most of what I was doing was same again, so I could copy and paste many elements from one package into another. So, I liked the idea of BIML from the beginning: most of the logic is repeatable and could be automated easily.

But the original BIML was heavy with programming from the beginning, and, from my experience, data engineers aren’t great in programming. It is a required skill, and all data engineers have it, but if we wanted to do programming, we would choose another career path. So BIML Flex is a perfect answer to that: less programming, more fun! I’m not sure how this would work if a lot of complexity is required, e.g. if script tasks are required etc. The project I’m at has a reasonable complexity and a lot of source tables that require very similar transformations, so I found BIML Flex is a reasonably good tool for the job.

For this project I needed some extra functionality, so I had to use Extension Points, which is BIML script injections in specific places. There’s a wide range of them available in BIML Studio. Oh, yes, this is one of the confusing parts, you still have to open the project in BIML Studio to generate SSIS packages.

There is an extra logging in my data warehouse. There is a procedure that generates an ID for each batch execution, and this ID is recorded in every row in the data warehouse. I find the easiest way to do it is to execute ID generation procedure in the batch pre-execution and pass the value to every package in batch as a parameter.

Batch pre-process extension point is easy and intuitive. In the menu pick BIML Flex -> Batch -> Batch Pre Process. This will create an example script in the Library -> Bundle Extensions. It is very easy just to edit this script for your function.

The batch name created in BIML Flex gets a “_batch” suffix when appears in BIML Studio. So, use the full batch name as appears in BIML Studio as a target:

<#@ extension bundle=“BimlFlex.bimlb” extensionpoint=“BatchPreProcess” target=“BOB_BUILDER_Batch” #>
<#@ property name=“batch” type=“BimlFlexModelWrapper.BatchesWrapper” #>

<Container Name=”SEQC – Generate batch ID” ConstraintMode=”Parallel”>
      <Tasks>
            <ExecuteSQL Name=”SQL – Get Batch ID” ConnectionName=”SQL_Bob” ResultSet=”SingleRow”>
                   <Results>
                          <Result Name=”0″ VariableName=”User.MyBatchID” />
                   </Results>
                   <DirectInput>EXEC [dbo].[GetMyBatchID]</DirectInput>
            </ExecuteSQL>
      </Tasks>
</Container>

I execute custom code, and the result of execution, my own batch ID, is recorded into the value of my user variable in runtime.

My next task was to add a package parameter to all packages in the bundle. There are two types of parameters in SSIS: project and package parameters. Project parameters could also be set via Extension Points. The solution for my task wasn’t trivial, so I had to get help from Varigence for that. In the menu I chose BIML Flex -> Object -> Package Variable. This also creates an example script. For some reason, if I want to apply this to all packages in the batch, I needed to use the original batch name from BIML Flex, without the generated suffix.

<#@ extension bundle=“BimlFlex.bimlb” extensionpoint=“PackageParameter” target=“BOB_BUILDER” #>
<#@ property name=“table” type=“BimlFlexModelWrapper.ObjectsWrapper” #>
<# CustomOutput.ObjectInherit = true; #>
<Parameter Name=”DataVersionID” DataType=”String”></Parameter>

It is important to keep the setting CustomOutput.ObjectInherit to true as this is how parameters get pushed to objects in the batch.

And the last step is to bind my user variable to the package parameter, so the value will be passed when the package execution is invoked from the batch. BIML Flex -> Batch -> Parameter Binding. In this script the full batch name with suffix should be used.

<#@ extension bundle=“BimlFlex.bimlb” extensionpoint=“ParameterBindings” target=“BOB_BUILDER_Batch” #>
<#@ property name=“batch” type=“BimlFlexModelWrapper.BatchesWrapper” #>

<ParameterBindings>
       <ParameterBinding Name=”DataVersionID” VariableName=”User.MyBatchID” />
</ParameterBindings>

From the package this parameter is available under its full name Package::DataVersionID, which I used in BIML Flex to populate derived column. In column’s definition under SSIS Dataflow Expression put @[$Package::DataVersionID] and don’t forget to tick the Derived box.

Kate
Data masseuse

‍