A Fine Slice Of SQL Server
Cathrine Wilhelmsen shows the easy way to build out a SELECT statement with Biml:
Biml column methods return code fragments. These code fragments can be used as building blocks to generate custom T-SQL statements. For example, the GetColumnList method returns a list of columns, separated by commas, that you can use in a SELECT statement. You can filter the columns and customize the output by passing parameters.
Biml is for more than just SSIS packages.
Comments closedBill Fellows describes the ability to execute code in Biml from a remote source with the CallBimlScriptContent function:
CallBimlScriptContent was introduced with the migration from Mist to BimlStudio. Why is this cool? You do not have to use files sitting on your computer as the source for your Biml. As long as you can reconstitute the Biml contents into a string, you can store your scripts where ever you’d like. If you want them in a database, that’s great. Store them in the cloud? Knock yourself out.
As a consultant, the latter is rather compelling. Maybe I’m only licensing my clients to use accelerators during our engagement. If I leave files on the file system after I roll off, or they image my computer and accidentally collect them, I am David fighting Goliath. CallBimlScriptContent is a means to protect myself and my IP. Let’s look at a trivial example. I set a C# string with an empty Package tag (hooray for doubling up my double quotes). Within my Packages collection, I invoke CallBimlScriptContent passing in my Biml content.
Bill’s use case was one I hadn’t thought about, but it does make sense.
Comments closedCathrine Wilhelmsen shows us you can do a lot more with Biml than just generating SSIS packages:
This actually happened to me in a previous job. We had a fairly complex ETL solution for the most critical part of our Data Warehouse. Many SSIS packages, views, and stored procedures queried the tables that were replicas of the source system tables. One day, we found out that the source system would be deploying a new version of their database the following day. In every single table, some columns were removed, others added, and many changed data types.
Oh.
There was no way that we could manually update all our SSIS packages, views, and stored procedures in less than a day. Thousands of users depended on our solution. It was too late to pause the source system changes.
Oh.
That story ends up with a happy ending.
Comments closedBen Weissman combines two of my favorite things:
If you have started playing with polybase, you probably figured out by now, that – as awesome as it is – it’s still a bit of a pain to set it up and maintain external tables. There is a wizard in Azure Data Studio but it’s still under development, especially from a usability standpoint.
So what can be done about that? Well, we effectively looking for an easy way to read metadata from a relational database and automate T-SQL to mirror that metadata. HELLO?! Perfect usecase for Biml – which is NOT just for SSIS.
Let’s take a look at how that can be done…
If only Ben could have used F# instead of VB and VB with curly braces…
Comments closedMy resources are quite simple: Excel Spreadsheet containing meta data, a driver program and a package template.
The template is your standard truncate and reload pattern with the target table being specified by a parameter. The client validates data by running processes in parallel so the existing mainframe process delivers data to the Billing table while ours delivers to a Billing_NEW table. Once they accept the new process, the target table becomes Billing and the NEW table is dropped. I decided the most native SSIS route would be use specify the target table in as a parameter. We originally have a boolean parameter indicating whether we were loading the new table or the production one but that was more logic and overhead that just specifying which table to load. I force their queries to be dirty reads as some of these queries can be rather messy.
Click through for the script and explanation.
Comments closedCathrine Wilhelmsen shows us how to avoid repeating our C# code using extensions:
In a previous blog post, we looked at how to use C#/VB Code Files in Biml. There are several benefits to moving custom C# code into separate files. It allows you to reuse that code across multiple projects and solutions. You can maintain the code in your editor of choice, taking advantage of intellisense and syntax highlighting. And finally, my personal favorite: you can create custom extension methods.
In this post, we will look at how to simplify our Biml projects by creating and using C# extension methods. We will build on the examples from the previous C#/VB Code Files in Biml blog post.
*pushes up glasses* You know, this would be even easier in F# and wouldn’t need extension methods.
Joking-not-joking aside, read the whole thing.
Comments closedCathrine Wilhelmsen gives us a workaround for no native OData support in Biml:
As of July 2018, there is no built-in Biml support for OData. To work with OData in Biml, you have to create a custom source and connection manager. This requires more Biml code than built-in functions like OleDbSource and may look a little overwhelming at first. But don’t worry! You don’t have to start from scratch.
In this blog post, we will first look at the properties of the OData Connection Manager and how to script it in Biml. Then, we will do the same for the OData Source. Finally, we will tie it all together and create a complete SSIS package that you can use a starting point for your own projects.
There’s a quick and easy solution but definitely read the whole thing to catch any oddities which might arise from reverse engineering your SSIS packages.
Comments closedAndy Leonard reports a bug as well as a temporary workaround for BimlExpress 2018:
I had no sooner published my blog post about the coolness of Biml 2018 when I encountered a bug trying to use one of the features I really like – converting SSIS packages to Biml using (FREE!) BimlExpress 2018.
My first response was, “Durnit! This worked in the test versions.” My second response was to drop a note into an issue-tracking site Varigence set up to record these kinds of things.
And then I started getting emails similar to, “Hey Andy, I get this error when I try to use the new ‘Convert SSIS Packages to Biml’ feature”…
David Stein has a workaround for us until Varigence can fix the bug.
Comments closedAndy Leonard has a bunch of new presents to unwrap:
Varigence keeps giving away cool stuff! Nowhere is Varigence’s commitment to community more evident than in the feature list for BimlExpress 2018. The previous version – BimlExpress 2017 – included the Preview Pane. BimlExpress 2018 includes the ability to Convert SSIS Packages to Biml:
How cool is that? And it’s in the free (FREE!) version!
As with BimlFlex and BimlStudio, there are too many cool features to list here. Head over to the BimlExpress 2018 feature page to learn more.
Converting existing packages to Biml was a great feature that I could never afford. It’s exceedingly nice of Scott Currie & crew to make that available in the free product.
Comments closedBen Weissman shows how you can use BimlStudio to build ADF v2 flows:
As you may have seen at PASS Summit 2017 or another event, with the announcement of Azure Data Factory v2 (adf), Biml will natively support adf objects.
Please note, that the native support is currently only available in BimlStudio 2018. If you’re using BimlExpress, you can still generate the JSON for your pipelines, datasets etc. using Biml but you cannot use the newly introduced tags.
The really good parts are only available in the paid product; if you do a lot of Azure Data Factory work, that might tip the scales in favor of getting BimlStudio.
Comments closed