A blog about SQL Server, SSIS, C# and whatever else I happen to be dealing with in my professional life.

Find ramblings

Friday, July 15, 2016

Building SSIS packages using the Biml object model

Programmatically building SSIS packages via the Biml Object Model

I thought it might be fun to try and figure out how to use the Biml Api to construct SSIS packages. This post is the first in the occasional series as I explore and find neat new things.

Getting Started

The most important precursor to doing this is you will need a licensed installation of Mist. Full stop. The assemblies we're going to use have security built into them to tell whether they are licensed and you cannot use the assemblies shipped with BidsHelper or BimlExpress as they've hardwired to the specific apps.

We're going to use two classes: AstRootNode and AstPackageNode.

Ast, what is that? Abstract Syntax Tree - it's a compiler theory thing.

AstRootNode? The root node is the <Biml /> tag. It contains all the collections in your biml declaration.

AstPackageNode? This is an instance of an SSIS package.

using Varigence.Languages.Biml;
using Varigence.Languages.Biml.Task;
using Varigence.Languages.Biml.Transformation;


            AstRootNode arn = new AstRootNode(null);
            AstPackageNode pkg = new AstPackageNode(arn);

            pkg.Name = "HelloWorld";

Now what?
You have two choices, you can get the Biml


which results in

<Biml >
        <Package Name="HelloWorld" />

Or you can get the Xml


which looks like

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
        <Package Name="HelloWorld" />

In this example, they're nearly identical except the Xml emission results in the namespace existing in the Biml declaration while the GetBiml call just returns Biml. Interestingly enough, if either is fed through BimlExpress, they'll both pass validation.

No comments: