THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Andy Leonard

Andy Leonard is an author and engineer who enjoys building and automating data integration solutions.

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

Published Tuesday, January 31, 2017 5:00 PM by andyleonard

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Chris Taylor said:

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

March 6, 2017 6:33 AM
 

andyleonard said:

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.

:{>

March 6, 2017 11:47 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

News

My Latest Book:

Community Awards



Friend of Red Gate

Contact Me

Archives

Privacy Statement