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.
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:
To begin, please open the text file named README_BimlExpressMetadataFramework.txt found in the Miscellaneous virtual folder:
This file will contain the latest execution notes for the solution:
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:
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:
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:
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:
At any time during this process, you may see a dialog similar to the one shown here:
If you see this dialog, always Select All and click the Commit button.
You may also see this dialog:
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:
If there are no errors, the SSIS Package named 30_Build_Destination_Tables.dtsx is generated:
Executing the Build SSIS Packages
Open and execute the SSIS Package named 10_Build_Destination_Databases.dtsx:
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:
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:
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:
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:
You will need to confirm your intention to delete these artifacts:
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:
The SSIS loader packages are generated.
A Controller SSIS package is also generated, named 99_Execute_AdventureWorks2014_Stage_Loader_Staging_Packages.dtsx:
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
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
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.
:{>
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
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.
Hi Thakks,
I have a very slow solution that desperately needs refactoring for performance. I based it on the Metadata Collector I describe in this post: https://andyleonard.blog/2008/08/ssis-design-pattern-collect-enterprise-sql-server-database-metadata-with-ssis/
Hope this helps,
Andy
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