Biml adoption routes

One of the reasons I like using Biml to generate SSIS packages is that that is no "wrong" approach to doing so. Instead, the usage and adoption of Biml should be tailored to the individual, team or organization that is using it. Despite my having used Biml for four years now, I still use it in the following ways based on the use case.

How does this work again?

As a refresher, Biml is XML that describes business intelligence artifacts. Specifically, we are going to use it to generate SSIS packages. This Biml is fed through the Biml compiler via BIDS Helper, BimlExpress, Mist, BimlStudio or a command-line compilation and SSIS packages are generated. Once generated, those SSIS packages will be indistinguishable from packages generated using BIDS/SSDT. There is no requirement to have any special server software installed to run these packages. This is entirely a developer-centric tool.

Forward only

For those just getting started with Biml, this is probably the best investment for their energy. Just this past June, I was working with a client on a very brief engagement where I was using SAP B/W as a source. Despite my best efforts, I couldn't get the CustomComponent properties "just right" for the emitted SSIS package to work.

Whatever your reason, this approach is that you will use Biml to generate as much of your SSIS package as you can and then finish coding it by hand. This is how my first few projects were implemented, by the way. For my SAP B/W packages, I stubbed in a dummy source in my data flow but the rest of my package was ready to go --- my auditing, logging, row counts, even the destination was ready. All I had to do with the designer was to open the package, replace the data flow source with the SAP B/W connector and double click the destination to have the columns route properly by name matching. Visually, I think of this approach looking like

There is a clean break between the source Biml and the package to show we've modified the generated object. If we were to regenerate, we'd have to reapply the same modifications to get the package back to the current state.

Cyclical

This approach is for those who are getting their feet under them and want to get it all "right." The arrow from SSIS back to the Biml file shows the cycle of

1. Modify Biml
2. Generate package
3. Test for correctness

I found this useful as I was learning the complete syntax for all the tasks and components I wanted to represent in Biml.

Metadata driven

This approach "puts it all together." From bottom to top, we take the Biml files we developed in the Cyclical phase and make them into "patterns." That doesn't have to be a complex endeavor, it could be as simple as putting a variable in for package name.

In the center, we have .NET scripts. This doesn't mean you need to be a developer and understand the intricacies of callbacks, lambda functions and asynchronous programming. If you can comprehend how to declare a variable and how to write a foreach loop, you know enough to get this done.

At the top is a metadata repository. Fancy words that mean "Excel". Or a CSV. Or a database table. Or any other place you might have recorded information that describes what you need to build. If the patterns are a cookie cutter, the .NET scripts the hand that pushes the cutter, then the metadata is your order telling you how many of each type of cookie to create.

All three of those work together to generate "flat" Biml which then takes the above route of being fed to the compiler and emitted as SSIS packages. You won't see the flat biml getting spat out, it's all going to be in computer memory but the process remains the same.

Use it

I think regardless of how you use Biml, it's worth your time to adopt it into your organization. It hastens your development speed, it drives consistency and there's no long-term commitment involved.