Biml Transformer Update a variable value

Biml

In preparation for Biml Hero training, I thought it would be a good idea to understand Biml Transformers. I have read that article many times but never really dug in to try and understand it, much less find a situation where it'd be something I needed. This post covers my first attempt to using one.

Use case: updating a variable value

Assume you have an SSIS variable in your Biml files that you need to update the value - the server died and you need a new server name patched in. You could do a search and replace in your biml, or apply a configuration once you emit and deploy the SSIS package but let's try the transformer.

Source biml

Let's use the following simple biml as our package. It's three sequence containers in a serial connection and a single variable ServerName with a value of SQLDEV.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Packages>
        <Package Name="OriginalPackage_LocalMerge" ConstraintMode="Linear">
            <Variables>
                <Variable DataType="String" Name="ServerName">SQLDEV</Variable>
            </Variables>
            <Tasks>
                <Container Name="SEQ 1" />
                <Container Name="SEQ 2" />
                <Container Name="SEQ 3" />
            </Tasks>
        </Package>
    </Packages>
</Biml>

Transformer biml

Our transformer is simple. We've specified "LocalMerge" as we only want to fix the one thing. That one thing, is an SSIS Variable named "ServerName".

What is going to happen is that we will redeclare the variable, this time we will specify a value of "SQLQA" for our Value. Additionally, I'm going to add a Description to my Variable and preserve the original value. The TargetNode object has a lot of power to it as we'll see over this series of posts on Biml Transformers.

<#@ target type="Variable" mergemode="LocalMerge" #>
<#
    string variableName = "ServerName";
    if (TargetNode.Name == variableName)
    {
#>
    <Variable DataType="String" Name="ServerName">SQLQA<Annotations><Annotation AnnotationType="Description">Value was <#=  TargetNode.Value#></Annotation></Annotations></Variable>
<#
    }
#>

So what's happening?

I think it's important to understand how this stuff works, otherwise you might not get the results you expect. The Biml compiler is going to take our source biml. If I had code nuggets in there, those get expanded and once all the "normal" biml is complete, then our transformers swoop in and are executed. This allows the transformers to work with the finalized objects, metadata is set and all that, prior to rendering actual SSIS packages.

Result

It doesn't look that amazing, I admit.

<Variable DataType="String" Name="ServerName">SQLQA
    <Annotations>
        <Annotation AnnotationType="Description">Value was SQLUAT</Annotation>
    </Annotations>
</Variable>

But conceptually, wouldn't it be handy to be able to selectively modify bits of a package? Someone didn't name their Tasks or Components well? You could have a transformer fix that. Someone forgot to add your logging/auditing/framework code? You could have a transformer fix that too!

Start thinking in Biml! Most of my answers on StackOverflow have biml in them simply because it makes describing ETL so much easier.

Caveats

Transformers require a license for Mist. They don't work in BIDS Helper, BimlExpress or BimlOnlien.