More Reasons to Adopt Biml

SSIS packages aren’t backwards compatible – that means if you start building packages targeting SQL Server 2012 and then find out you need to also support and earlier version (like what happened to me this week), you will need to start over. However, if you used Biml to generate your packages you could spin up an instance of SQL Server 2008 in Azure, load your Biml project in BIDS and regenerate your packages targeting a different version.

You can change quickly – I recently found myself in the situation where I had developed an Extract, Transform and Load package and when it came to test them realize that they were wrong. I need to load data after it had been transformed because down stream Transforms depended on data being loaded. Here’s a picture

1-Before

What I had to do was combine the Transform and Load packages into a single one. I could have gone through and copy, pasted, joined up and fixed any problems for each and every one (there were about 30+ transforms), but that would have taken a while and been a very boring task. Instead I made a very minor change to my Biml package and then regenerated the package.

2-After

You can refactor quickly – this is in a similar vein to the point above. In another project I did my development against SQL Server 2012 when it came time to deploy I found out the client site I had to deploy to was still running SQL Server 2008. Which meant I had to say good bye to the Project Deployment model, along with the nice and easy configuration experience you get with 2012 and go back to using XML configuration files – this was a bit of a pain to figure out. Given that I had about 40 packges, the effort of going through each one to enable Package Configurations would have been immense (and boring), instead after I figured out how to do it in Biml all I needed to do was regenerate the packages.

Drag and drop doesn’t scale – it works fine for small ad-hoc packages that you end up throwing away, however I strongly hold the view that if you’re doing any serious development, building packages by hand just isn’t going to scale. This is especially true when all you’re really doing is implementing design patterns. I don’t remember the last time I dragged and dropped something in SSDT BI, and I have been doing a lot of SSIS development recently!

In conclusion and the main reason you should adopt Biml, your time scales better. All the things I have outlined above I could have done by hand, but it would have taken me a while and would have been boring. I probably would have made mistakes along the way too. Instead, by using Biml to create the packages I can concentrate on more important things (like writing this blog post, automating our database deploytment or automating our release process).

Advertisements

The Unexpected Consequences of Automating our Deployment

Six month ago when I joined DrDoctor we were doing manual deployments into production. It would take us somewhere between two and three days to prepare for, test and release into production. We had no Continuous Integration environment and nowhere to test or demo changes before releasing.

Today it is a very different picture. Each change that we push to our BitBucket repository is picked up by TeamCity, which then does a build and runs an extensive test suite. The build is then made available to Octopus Deploy which we use to deploy into a test environment that very closely mirrors our production server, we can then promote the release to Production.

Here are some of the unexpected consequences that have come about as a result:

  1. The quality of the User Stories that our product owners produce has dramatically increased. Our stories are now (generally) focused on a vertical slice
  2. When starting new areas of work we factor in the time required to set up the deployment steps necessary to release into production
  3. We started off with a test server that kind of looked like our production server, we’ve invested a lot of time in making it mirror production as close as possible
  4. We break things from time to time, but we’re okay with that – when we do break things we retrospect and figure out how to avoid it happening that way again
  5. We try to be mindful of making architectural decisions that are going to fit with releasing often
  6. We are actually able to pursue continual improvement – but after a while we became stuck as to what we needed to improve so we created a release diary which we fill in each time we deploy into production to identify pain points
  7. Our deployment system (Octopus Deploy) has become living documentation about our production environment
  8. At the beginning deploying database changes was too hard to automate, so we stuck with a manual process. Over time we’ve figured out how to automate right up unto the point of applying the changes
  9. We had the mantra – deploy often – but then we found ourselves in the habit of bunching up lots of changes, which made it harder to deploy. To fix this we gave ourselves three days from the time a story had been accepted by the Product Owner (that means demoed in our test environment) to releasing it into Production – we don’t always hit it but we try our best
  10. Releasing into production is no longer the job of the individual who had the most experience and knew all the quirks, rather it is something that everyone on the team can all do, with confidence

If you’re interested in how to get started with automating your deployments leave a comment, I would be very happy to help you along the way.

BIML – Passing Variables to Child Packages

A project I’ve been working on recently has consisted of lots of little packages which are called from a single coordinating package. I started off developing against SQL Server 2012, and thankfully used BIML to generate all my packages. The main controller package passes a variable down to the sub-packages, which is then subsequently used when executing a stored proc. This all worked nicely until I was told that it needed to work against SQL Server 2008 😦 Thanks to the ease with which BIML allows changes to be made I had it fixed and working again within minutes.

This post will show how to pass variables from a parent package to a child package for both SQL Server 2012 and SQL Server 2008.

SQL Server 2012

This is actually really easy for SQL Server 2012, thanks to the introduction of package parameters. The below BIML will generate two SSIS packages.

Parent – This package has a variable defined as ParentVar, the Execute Package data flow task will pass this variable as the value for the ChildVar parameter defined in the Child package.

Child – This package has a single parameter defined, ChildVar which will have the value filled in by the Parent package.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Packages>
    <Package ConstraintMode="Linear" Name="Parent">
      <Variables>
        <Variable DataType="String" Name="ParentVar">FromParentPackage</Variable>
      </Variables>
      <Tasks>
        <ExecutePackage Name="Execute Child">
          <ParameterBindings>
            <ParameterBinding VariableName="User.ParentVar" Name="ChildParam"></ParameterBinding>
          </ParameterBindings>
          <ExternalProjectPackage Package="Child.dtsx"></ExternalProjectPackage>
        </ExecutePackage>
      </Tasks>
    </Package>
    <Package ConstraintMode="Linear" Name="Child">
      <Parameters>
        <Parameter DataType="String" Name="ChildParam"></Parameter>
      </Parameters>
      <Tasks>
        <Container ConstraintMode="Linear" Name="DoStuff"></Container>
      </Tasks>
    </Package>
  </Packages>
</Biml>

SQL Server 2008

In SQL Server 2008 we don’t have the luxury of package parameters so we have to fall back to Package Configurations. To pass a variable to a child package we need to define a “Parent package variable” configuration, which tells the Child package to expect a given variable from the Parent package and which local variable it should be mapped to.

Once again the following BIML will generate two packages.

Parent – This package has the variable defined and set, as well as an Execute Package data flow task which will execute the Child package

Child – This package has a Package configuration which will map the ParentVar variable to the local ChildVar variable. You can see the Package Configurations by right clicking anywhere in the package control flow and then clicking Package Configurations, this will bring up the following:

Package-configurations

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Packages>
    <Package ConstraintMode="Linear" Name="Parent">
      <Variables>
        <Variable DataType="String" Name="ParentVar">FromParentPackage</Variable>
      </Variables>
      <Tasks>
        <ExecutePackage Name="Execute Child">
          <Package PackageName="Child"></Package>
        </ExecutePackage>
      </Tasks>
    </Package>
    <Package ConstraintMode="Linear" Name="Child">
      <Variables>
        <Variable DataType="String" Name="ChildVar" InheritFromPackageParentConfigurationString="ParentVar"></Variable>
      </Variables>
      <Tasks>
        <Container ConstraintMode="Linear" Name="DoStuff"></Container>
      </Tasks>
    </Package>
  </Packages>
</Biml>

(To use this sample, make sure that the project you generate the packages in is set to the “Package Deployment Model”)