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.
Previously, you had to declare any kind of variable and object type (instead of just using something like “var” in C#):
integer = 1 to 25
That was true even for the most simple cases like for n as integer = 1 to 25 instead of just for n = 1 to 25, even though it is clear that his can never be anything else in this context.
Now, you can use two new attributes in the template definition: optionexplicit and/or optionstrict
Read the whole thing if you want to write VB code in Biml. If you want to write C# code in Biml, keep doing your thing. If you want to write F# in Biml, the pitchfork mob is organizing over here.
After building connections, you need to build the file formats. 01_FileFormats.biml calls the interrogator class I built and tries to guess the structure of the files. The script takes that information and then builds the Biml structure representing the layout of any csv or txt files it finds in SourceFolder. You can run this like the connections, Ctrl-click 00_GetOutput and 01_FileFormats, then right-click on the highlighted area and choose Generate SSIS Packages. You can then see the FileFormat nodes in output.biml.
After you have a biml representation of FileFormats, you can then generate the connections for the FlatFiles. FlatFile connections have a required attribute for FileFormat. To see the code for the connections, you have to select 00_GetOutput, 01_FileFormats, and 02_Connections-FlatFiles. Then output will contain both the FileFormats and Connections to the flat files!
Read on for more details.
Once you’ve installed BimlExpress 2017 and open your first Biml file, you will probably notice immediately, that the screen is split horizontally – that is exactly for the preview pane.
If you need more real estate for your actual code, just click the „Hide“ button at the lower left corner.
To actually get a preview, click the „Update“ button on the lower right:
That’s a pretty nice feature. It can be hard sometimes to debug Biml issues because you’re often writing code to write code to write code.
One of the awesome new features in Biml is a new directive called “global”! It does exactly, what you would expect it to do: it allows you to add code to all or some of your Biml files at once.
Here is an example: This file with only 2 lines will make VB your default language across your entire solution!
I’d prefer an F# global directive, myself… But this looks like a very useful addition to Biml.
2. Use the Hex Code
If you know the ASCII Code of your qualifier, you can use it starting with “_x” and ending with “_”.
A ” would be described by “_x0022_” for example.
Click through for the other two, as well as a bonus side-by-side German translation.
One of the first things I started wondering about as I got used to reading OPC (other peoples code) is just EXACTLY what is BIML doing at any given point in the code. You can make some educated guesses based on the SSIS packages (in my case I’m exclusively interested in BIML for SSIS but of course it can do a lot more than that), but it’s easy to get lost, especially when there’s a lot of BIML script and some of it is only used to establish a data model in memory or to create / fill variables that will be used in SSIS. I was delighted to discover the following piece of code that can show you exactly what BIML is doing based on the code you are writing.
If you don’t have BimlStudio, this trick is vital for figuring out what’s going wrong.