Ben Weissman has a pair of posts regarding metadata models in Biml. First up, he gives us the high-roller solution:
If you’re lucky enough to be a BimlStudio user, you have access to the Biml Metadata feature! This feature allows you to build a Metadata model that fits your exacts needs which can then be browsed and used through a Metadata Instance using a dynamic object model.
As you probably still want to maintain your metadata outside of BimlStudio, we’ve build this little piece of code. It will ready your meta-Schema from a given SQL Database and build a Biml Metadata-Model from it. In a second step, it will also import the contents of your model into an instance:
If your company doesn’t want to shell out the cash to buy a license for BimlStudio, Ben also has a version for people using the free BimlExpress tool:
So maybe you’ve noticed our blog post on deriving metadata from SQL Server into BimlStudio, but you are using BimlExpress and therefore don’t have access to the feature described in there? While it’s true, that BimlExpress doesn’t support the Metadata features of the Biml language, there are similar ways of achieving a flexible metadata model in Biml.
This post shows you, how you can build a model in SQL Server, import it to Biml including derived relationships etc. and use it in a very flexible way.
To get started, we need to set up a sample model in SQL Server first. You can either download the scripts from https://solisyon.de/files/SetupDerivedMetadata.sql or scroll to the very end of that page. Although your individual model can and will differ from this one, we suggest you follow along using our example and start tweaking it afterwards!
Once you really get how Biml converts metadata to packages, life gets so much easier.
Next, I wanted to build a package from Biml. A package that uses a connection to my Oracle instance. Defining connections to Oracle in Biml is similar to every other connection you’ve ever defined in Biml (lines 3-12). The difference here is the Provider. (line 10) I took the Provider attribute for my Oracle connection from the manual package I created in my previous test.
Simply right click on your Oracle connection in the package and choose properties. Then look at the Qualifier value. I take the whole value and copy and paste it into the Provider attribute in my BimlScript.
Once you have that defined, set up a Package with our two connections, and a data flow (lines 14 – 33). This script builds a simple data flow to move data from a single table on Oracle over to SQL Server. The only difference is, this time, the package is named Automatic, rather than Manual.
You could get as complex as you’d like in your example.
As far as Integration Services goes, Oracle is just another source or sink and as long as you have an appropriate driver, the flows work the same way as with any other database.
The table meta.containers could technically also be a temporary table. We’ve decided against that so you can see what’s happening behind the scenes.
Let’s focus on the meta.tables table for now. It has three columns:
– TableName – guess what we’ll store in there
– Container – this one will hold the information, which container we want this table to be loaded it, which will be automatically populated by our stored procedure
– Cost – this column will hold the load cost of this specific table. In our opinion, this should ideally be the average time it took SSIS to load this table in the recent past. If you don’t have that information available, it might as well something like the size of this table in either Gigabytes or Rows. The more accurate this column is, the better your results will be.
The only tricky part in Ben’s code is figuring out appropriate values for Cost, but if you’ve got rough timing measures or even good priors, you can get to a reasonable solution quickly. And if time is of the essence, you can model, simulate, and apply results as part of an analytics project.
Good grief, that’s a lot of code, how do I use it? The basic usage would be something like<Tables> <#= GetAstTableNodeFromQuery(this.RootNode.OleDbConnections["msdb"], "SELECT 100 AS demo", "dbo", "DemoQuery").GetBiml() #> </Tables>
The call to GetAstTableNodeFromQuery return an AstTableNode which is great, but what we really want is the Biml behind it so we chain a call to
.GetBiml()onto the end.
What would make that better though is to make it a little more dynamic. Let’s improve the code to create tables based on a pairs of names and queries. I’m going to use a Dictionary called namedQueries to hold the names and queries and then enumerate through them, calling our GetAstTableNodeFromQuery for each entry.
Read on for the underlying code. Bill also promises to refactor this code and make it more versatile.
One of the questions I get when teaching others how to use Biml is how do you deal with sensitive information like usernames and passwords in your Biml Solution. No one wants to leave this information in plain text in a solution. You need access to it while interrogating your sources and destination connections for metadata. You also need it while Biml creates your SSIS packages since SSIS uses SELECT to read the metadata during design time to gather its metadata. If you lock away that sensitive information too tightly, you won’t be effective while building your solutions.
In the end, you’ll have to compromise between security and efficacy.
Read on for more.
One great way to introduce default values in Biml would be variables in include files or code files for example. But depending on what you’re trying to achieve or at what point you realize it, it may already be causing some extra work.
For example: You have a couple of diffent ways to create a dataflow task but in the end, they should all share a property like DefaultBufferMaxRows.
In BimlStudio, you could make use of a transformer, but these are not available in BimlExpress.
As a bonus, this is a bilingual post on two fronts, so you can pick up a little English-German translation as well as a little VB.Net-C# translation.
In many of the walkthroughs on creating relational objects via Biml, it seems like people skim over the Databases collection. There’s nothing built into the language to really support the creation of database nodes. The import database operations are focused on tables and schemas and assume the database node(s) have been created. I hate assumptions.
Read on for more about dealing with databases, and not just tables and other database objects, in Biml.
My development environment has a local instance of SQL Server with AdventureWorks2014 on it. I’m going to use that as my source. I also created a database on this instance called BimlExtract to serve as my destination database.
To create a user that can only read the schema on the source system, I created a login and user named ‘Biml’. I granted this user VIEW DEFINITION in AdventureWorks2014. I also added this user to the db_owner group in BimlExtract. Now, this user can read the schema of the source, and create tables in the destination. I’ve included the T-SQL to set the permissions in Database Setup.sql.
Now, we’re ready to walk through the solution.
Click through for the solution and also a GitHub repo with all of Shannon’s code.
Wait, what? .NET intellisense? Does that mean C# and VB intellisense? You bet it does!
Now, let’s start with a small disclaimer. The release notes say: “Added preview mode of .NET intellisense for early adopters”. That means that this feature is not available out of the box for everyone, it has to be enabled per product key by Varigence. So how do you get it? It’s very simple: E-mail Varigence. Help them out by providing feedback and suggestions for improvements. If you want to go crazy, you may even mention BimlExpress in social media, blog posts or if you’re presenting somewhere. But that’s not a requirement. Just e-mail Varigence and ask. They’re nice guys 🙂
Cathrine also has a webinar coming up tomorrow on the topic.
So far, there wasn’t much you could do but either replicate a lot of logic, parse the XML returned, use countless annotations or have many many different versions of your callee.
All of these options aren’t too appealing, right?
But: help is here! In addition to the known CallBimlScript, you can now use: CallBimlScriptWithOutput! It allows you to make use of a dynamic object to be returned by the CallBimlScript (in addition to the Biml code) which effectively means: you can return any kind of information back to the caller.
I can see this being useful for debugging and for centralizing flow logic a little bit better. Click through for a sample script.