BimlExpress Metadata Framework

I’m not good at naming things. “Biml Express Metadata Framework” is descriptive but the acronym – BEMF – is difficult to pronounce (for me, at least). Kent Bradshaw wants to name it George. We’ll keep working on a name…

What is the BimlExpress Metadata Framework?

The Biml Express Metadata Framework uses Business Intelligence Markup Language (Biml) to:

  • Read metadata stored in a SQL Server database.
  • Build SSIS projects that create the target database, schemas, and tables.
  • Build an SSIS project that contains one SSIS package per table, plus a Controller package that executes each table-package.

bemf_0

I demonstrated this solution in the webinar Save Time and Improve SSIS Quality with Biml. It’s free; both the webinar recording and the framework (although registration is required to view the webinar recording). It’s the latest addition to the DILM Suite. The zip file contains a backup of the AdventureWorks2014 database, BimlMetadata database, and the SQL Server Data Tools (SSDT) solution BimlExpressMetadataFramework.  You may download the zip file here.

Once open, the SSDT solution appears as shown here:

bemf_1

To begin, please open the text file named README_BimlExpressMetadataFramework.txt found in the Miscellaneous virtual folder:

bemf_2

This file will contain the latest execution notes for the solution:

bemf_3

Generating the Build Destination Database SSIS Package

To generate the SSIS Package named 10_Build_Destination_Databases.dtsx multi-select the Biml files 0_00_BuildConnections.biml and 0_10_BuildDestinationDatabaseMain.biml. Right-click the selected files and click Generate SSIS Packages:

bemf_4

If there are no errors, the Project Connection Manager named __master__AdventureWorks2014_Stage.conmgr and the SSIS Package named 10_Build_Destination_Databases.dtsx are generated:

bemf_5

Generating the Build Destination Schemas SSIS Package

To generate the SSIS Package named 20_Build_Destination_Schemas.dtsx multi-select the Biml files 0_00_BuildConnections.biml and 0_20_BuildDestinationSchemasMain.biml. Right-click the selected files and click Generate SSIS Packages:

bemf_6

If there are no errors, the Project Connection Manager named AdventureWorks2014_Stage.conmgr and the SSIS Package named 20_Build_Destination_Schemas.dtsx are generated:

bemf_7

At any time during this process, you may see a dialog similar to the one shown here:

bemf_9

If you see this dialog, always Select All and click the Commit button.

You may also see this dialog:

bemf_10

If you see this dialog, always click the Yes to All button.

Why? The BimlExpress Metadata Framework was not originally developed for BimlExpress, the free SSIS package code-generation tool from Varigence. Originally, this framework was built in Mist (Biml Studio) and ported to BimlExpress. One unfortunate result of the port was some SSIS artifacts are recreated during the process (note: this does not happen in the Commercial version of the Biml Framework, currently sold as an implemented solution available from Enterprise Data & Analytics).

Generating the Build Destination Tables SSIS Package

To generate the SSIS Package named 30_Build_Destination_Tables.dtsx multi-select the Biml files 0_00_BuildConnections.biml and 0_30_BuildDestinationTablesMain.biml. Right-click the selected files and click Generate SSIS Packages:

bemf_8

If there are no errors, the SSIS Package named 30_Build_Destination_Tables.dtsx is generated:

bemf_12

Executing the Build SSIS Packages

Open and execute the SSIS Package named 10_Build_Destination_Databases.dtsx:

bemf_11

If the database already exists, the SSIS Package named 10_Build_Destination_Databases.dtsx will succeed and do nothing.

Open and execute the SSIS Package named 20_Build_Destination_Schemas.dtsx:

bemf_13

If the schemas already exist, the SSIS Package named 20_Build_Destination_Schemas.dtsx will succeed and do nothing.

Open and execute the SSIS Package named 30_Build_Destination_Tables.dtsx:

bemf_14

There’s an Execute SQL Task for each table. The T-SQL statement in each Execute SQL Task drops the table if it exists and then creates the table. Once created, the database, schemas, and tables appear in SQL Server Management Studio (SSMS) Object Explorer:

bemf_15

Delete the existing SSIS artifacts. This is another side-effect of porting this framework from BimlStudio to BimlExpress. Before proceeding, we need to delete the existing Project Connection Managers and SSIS Packages:

bemf_16

You will need to confirm your intention to delete these artifacts:

bemf_17

Generating the SSIS Project That Contains the Loader SSIS Packages

To generate the SSIS packages that load the AdventureWorks2014_Staging database, multi-select the following Biml files:

  • 0_00_BuildConnections.biml
  • 0_10_BuildDestinationDatabaseMain.biml
  • 0_20_BuildDestinationSchemasMain.biml
  • 0_30_BuildDestinationTablesMain.biml
  • 1_99_Main.biml

Right-click the selected files and click Generate SSIS Packages:

bemf_18

The SSIS loader packages are generated.

bemf_19

A Controller SSIS package is also generated, named 99_Execute_AdventureWorks2014_Stage_Loader_Staging_Packages.dtsx:

bemf_20

The Controller package uses Execute SSIS Package Tasks to call the SSIS loader packages.

Conclusion

The BimlExpress Metadata Framework may not have the coolest name but it’s free and performs a lot of work in a short amount of time.

:{>

Learn More:
Save Time and Improve SSIS Quality with Biml
SSIS Catalog Compare v2.0 Launch Event 7 Mar 2017!
The Basics of Biml – Populating the Biml Relational Hierarchy

Related Training:
IESSIS1: Immersion Event on Learning SQL Server Integration Services – April 2017, Chicago 
IESSIS2: Immersion Event on Advanced Integration Services – Oct 2017, Chicago
From Zero to Biml – 19-22 Jun 2017, London

Andy Leonard

andyleonard.blog

Christian, husband, dad, grandpa, Data Philosopher, Data Engineer, Azure Data Factory, SSIS guy, and farmer. I was cloud before cloud was cool. :{>

6 thoughts on “BimlExpress Metadata Framework

  1. Hi Andy,
    do you have any indication as to when Varigence will fix the "Object reference not set to an instance of an object" bug in BIMLExpress as per: https://www.varigence.com/Forums?threadID=9625
    I’m not sure what differences you have in your BIML code which makes your scripts work but for myself and many others a master/child package setup doesn’t work.
    regards,
    Chris

  2. I know the good people at Varigence are working hard on the next release. Like Earnest and Julio Gallo, they will release no software before its time. Hang in there, sir.
    :{>

  3. Hi Andy,
    Thanks a lot for making this framework public. I downloaded it and have it running in our dev environment and the tests look very promising. Before I go ahead and create my own stored procedures to fill in meta-data, may I ask how you usually go about it? One approach as I see it is to manually fill in metadata just at the connection and database level and then have a SSIS package grab metadata for all tables/views/columns in the source to populate the other metadata tables. Then one could just use an update script to set the GenerateBimlFlag’s for the tables needed and a trigger or sp to set the lower level flags. Of course one would still have to manually update the configurables (for example IsColumnBusinessKey) but most of the metadata would generate automatically.
    Another approach would be to import all neccessary tables once and then use those tables to generate all metadata at the table level or lower. So instead of reading metadata from the source it would be read at the destination.
    I guess you use the RefreshMetadata flag to hint at your metadata-flow on which metadata should be updated and which should stay static, but I haven’t seen where you reference it – is your own metadata flow publicly available?
    Sorry if the above was a bit unstructured but am just getting started with BIML and am very excited 🙂
    Best regards,
    Asbjørn

  4. Hey
    Did you managed to get any working solution that allow to populate the metadata as you mentioned. I am sure someone might have the code in sleeve where you point to the source database or destination database and populate all the columns and table metadata.

    1. Hey Andy
      Excellent will have a go with it. As my source database is not MSSQL, and connecting through ADO.NET driver i will need few tweaks on this solution. But destination is MSSQL, so should be good for that.
      I was struggling to get the column mapping working when the column name on the Source and Destination is different. I am not quite sure what am i missing. When i change the column name in your solution on this blog, it doesnt map the column based on the mapping table! Any idea where am i going wrong

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.