Press "Enter" to skip to content

Category: Biml

Processing JSON in Biml

Bill Fellows takes us through a library which (seemingly by law) must be in every .NET project:

#sqlhelp #biml I would have the metadata in a Json structure. How would you parse the json in the C# BIML Script? I was thinking use Newtonsoft.Json but I don’t know how to add the reference to it

Adding external assemblies is a snap but here I’ll show how to use the NewtonSoft Json library to parse a Json based metadata structure and then use that in our Biml.

Click through to learn how.

Comments closed

SSIS Design Preferences

Meagan Longoria systematizes a set of preferences regarding Integration Services package and ETL process design:

– Every table should have InsertDateTime and UpdateDateTime columns. The UpdateDateTime column should be populated with the same value as the InsertDateTime column upon creation of the row, rather than being left null.
– Whatever you use to create tables, include primary keys, foreign keys, and indexes with your table definitions. Provide explicit constraint names to simplify database comparisons. You can disable your foreign keys, but they need to be there to provide that metadata.
– Separate your final dimensional/reporting tables from audit tables and staging tables. This can be done with separate schemas or even separate databases.

People have added some more thoughts in the comments as well.

Comments closed

Biml Support in Visual Studio Code

Cathrine Wilhelmsen takes us through Biml support in Visual Studio Code:

Please note that you only get syntax highlighting with this extension. You do not get the full Biml or .NET intellisense, the BimlScript preview pane, or the ability to generate SSIS packages from Biml. For those things, you will still need BimlExpress for Visual Studio.

However! If you simply want to view your Biml files in a lightweight editor, the Biml Support extension works beautifully

It’s not full support, but it’s something.

Comments closed

Importing Biml Metadata from Excel

David Stein wraps up a series on using Biml to load flat files:

Make sure you read and understand the concepts in each of the following articles before tackling this one.

Import Biml Metadata with GetQuerySchema
BimlScript Code Nuggets and Mad Libs
Import Biml Metadata Directly from Excel

One of the primary issues I’ve mentioned in each of the preceding short cut steps is a lack of real automation. Since we didn’t have our metadata externalized, we needed to copy/paste some object, T-SQL fileBiml file, or Excel Spreadsheet in order to create new extracts.

The final post in the series contains quite a bit of code, too.

Comments closed

Importing Biml Metadata from Excel

David Stein shows how you can take table and column data from Excel and use it to populate Biml flows:

Excel Spreadsheets as a metadata source have a lot going for them.
– Everyone uses Excel and is comfortable with it.
– Excel is incredibly customizable and versatile.
– Excel offers data validation and filtering.

For these reasons, I create customized Excel spreadsheet that function as a lite Graphic User Interface (GUI) for metadata. Of course, Excel isn’t a perfect metadata source. For one thing, you have to own a licensed copy of Excel. Second, because spreadsheets are so easy to customize, users sometimes “improve” them further and break your code.

Read on for an example.

Comments closed

Using Biml to Retrieve Query Metadata

David Stein shows how you can use the GetQuerySchema() function in Biml to retrieve metadata for a query:

This post is part of a series, Short Cuts to Biml Mastery which shows you how to learn BimlScript through a series of short cuts and remain productive while doing so.

The GetQuerySchema method provides an excellent way to import metadata from a query, in a similar way as GetDatabaseSchema imports schemas, tables, and views. GetQuerySchema was introduced last year with the 2018 editions of BimlStudio and BimlExpress.

This kind of higher-level programming takes some getting used to, but once you understand it, you can find common data access patterns and build one solution to work through many versions of the pattern.

Comments closed

BimlExpress 2019

Cathrine Wilhelmsen digs into BimlExpress 2019:

This is the first major BimlExpress release this year, called the R1 release. There are no new features in this release, but BimlExpress now supports both Visual Studio 2019 and SSIS 2019!

While there are no new features in this release of BimlExpress, there are two changes to Visual Studio that you should be aware of as a Biml developer.

Read on to see what those two changes are.

Comments closed

Simplifying Imported Biml

David Stein takes us through an example of what imported Biml looks like and how you can make it better:

This post is part of a series, Short Cuts to Biml Mastery which shows you how to learn BimlScript through a series of short cuts and remain productive while doing so. In the previous article, I covered how to import existing packages into Biml using BimlExpress. In this article, we’ll examine that Biml and simplify it in preparation for automation. If you’ve been reading along in this series, you’ll remember that we started with a single SSIS package which extracts data from a SQL source and creates a CSV file with quote identifiers. 

Read on to see the example and David’s first steps toward improving it.

Comments closed

Converting Existing SSIS Packages to Biml

David Stein shows off a conversion tool built into BimlExpress:

BimlExpress is a free Visual Studio add-in created by the good folks at Varigence. Its a full featured Biml editor which allows you to dynamically create SSIS packages. It was first released back in 2017, and the latest version is 2019 (of course). The current version supports Visual Studio 2010 through 2019 as well as SQL Server 2005 through 2019.

Prior to it’s release, Biml was written with Bids Helper, now known as BI Developer Extensions. While BI Developer Extensions has many nice features, you should no longer use it to work with Biml as it is no longer being updated/supported.

I’m pleasantly surprised by this. It used to be limited to BimlStudio (nee Mist) and BimlOnline.

Comments closed

Generating SELECT Statements with Biml

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 closed