Today I was working with the Derived Column BIML transformation. The larger task is a CDC based load pattern from a metadata framework database using BIML to generate the packages. Because I need this to be applicable to clients of any size, I’m trying to avoid the cost of the richer set of tools. Here in New Zealand it is not as easy to justify the price tag for an engagement that may only last a week or two.

This does mean there is a certain amount of hand-crafting happening here. But that is okay, because when your hands are covered in bits and bytes as you’re sweating over code you’re building that foundational knowledge and understanding of the layers. As a result you’re able to to pick up any problems long before they occur.

The example BIML snippet for today deals with Derived Columns, which is a fairly common task in any SSIS package.

Derived Column examples:

The declaration follows a standard BIML pattern. You declare the Task and name it. Remember that names follow the standard pattern for SSIS tasks, so be sure it is not only unique but descriptive. You can see an example below.

<DerivedColumns Name="Add System columns"/>

To make it useful the Derived Column must actually add columns into our data flow. The Derived Columns XML node contains a Columns container, which is a collection of Column nodes. To make that a bit more visual:

Consider the Column nodes to be the rows in the grid and the Columns container to be the actual grid. They are specified as per the below:

<DerivedColumns Name="Add System columns">
    <Columns>
        <Column Name="__$BatchID" DataType="Int32">@[ETL::BatchID]</Column>
        <!-- Add more Column nodes here as needed -->
    </Columns>
</DerivedColumns>

As you can see, it is a reasonably simple object to use. Remember though the Value specification for it does not follow the same pattern as parameters. You need to specify that exactly as you would the “Expression” piece of the SSIS Task. That was the only bit that caught me out with the Derived Columns task, although a quick “Check for Errors” does highlight the pattern.