Biml Express 2017 tests, comments and work-arounds
The new version of Biml Express, the free script-based ETL generation plug-in for Visual Studio provided by Varigence, has been out for a few months. Mid-July 2017 to be precise. However only recently I have been able to find some time to properly regression-test this new release against my library of patterns / scripts. The driver is the upcoming Data Modelling Zone event and Data Vault Implementation & Automation training sessions – better keep up to date!
This new release comes with some neat improvements in the compiler including updates in .Net support (4.6) and runtime performance. Probably the preview pane is most noticeable – this essentially the GetBiml() / expand function in Biml Studio and allows to preview the raw script in ‘pure Biml’ (results after C# or VB operations).
What I really like is the support to generate true annotations in the ETL for documentation. I’m a big fan of generating documentation wherever possible, and with this new release you can add annotations (textbox-like) to your generated ETL. It was already possible to generate annotations to various components but these were only visible as properties and since there are so many properties this was not always immediately visible.
A great example of how this works is outlined on this blog post by Cathrine Wilhelmsen: https://www.cathrinewilhelmsen.net/2017/07/17/bimlexpress-2017/.
During the regression tests I only found one issue that required a work-around: in this new version it is not possible anymore to generate a Data Type Conversion component with output attributes using the same names as the input attributes. In the scripts I use there is a component that maps attributes to their generic counterparts (for example varchar(3) to varchar(100) as a standard), and this used to just overwrite the data type for the same attribute name.
This doesn’t work anymore; the <SourceColumn> name now needs to be different from the <TargetColumn> name. This isn’t immediately obvious as the only error you will get is a NullReferenceException and a note that the packages can’t be emitted. If you find yourself encountering this issue, check that input and output names are different. It took me a good few hours to pin this down so hopefully this will help someone out there.
If you have a very large solution (i.e. a Visual Studio solution with many generated SSIS packages), you are likely to experience delays as Biml Express will automatically compile all Biml scripts in the solution every time you make changes and save. This also means that if you have C# code such as MessageBox running, you will see a lot of pop-ups even thought you’re not actively generating packages. There is no way to disable this, since Biml Express is not really meant for big production environments. In some cases I know Bids Helper is preferred for this reason, although this means not having intellisense and syntax highlighting functionality. Arguably this is a good time to look into Biml Studio.
There are tiny differences between the Bids Helper and Biml Express engine that are easily fixed as to date everything that works in Biml Express seems to work in Bids Helper, but not the other way around. Better to stick to what works in Biml Express then… An example is outlined in this previous post; this is still a known issue in the latest version. Other examples are using ‘true’ and ‘false in various settings as opposed to 0’s and 1’s, i.e. when using options such as UseFastLoadIfAvailable, TableLock and CheckConstraints. Nothing major, but it may cause some frustration the first time you encounter this.
Biml Express can be downloaded here: https://www.varigence.com/BimlExpress.
Many thanks for Varigence for keep updating and providing this awesome technology!