Diesen Artikel finden Sie hier auch in deutscher Sprache.

So you’ve decided to go all in with a metadata driven Biml solution to drive your staging processes – great! But you already have a staging area, so it seems kind of silly to manually create all that metadata from scratch, right? Then again, this would be a one-time task and Biml is only for ongoing and repeatable tasks. WAIT! No – it’s not! In many cases it makes perfect sense, to use Biml even for a task that you’ll only execute and need once, just like reverse engineering your metadata.

Your datamodel may look different so feel free to adjust the T-SQL to your needs, but let’s assume, that you hold the table related metadata in a table like this:

CREATE TABLE [dbo].[MyBimlMeta_Tables](
	[ConnectionName] [nvarchar](50) NULL,
	[SchemaName] [nvarchar](50) NULL,
	[TableName] [nvarchar](50) NULL,
	[Columns] [nvarchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

For each table that we will get from any of our sources, we will have one row.

We assume, that you have an existing staging database following these design guidelines:

– Columnnames in the database match those of the respective source
– Tablenames will be <SRC>_<SCHEMA>_<Table>, unless the source schema is DBO, in which case it will just be <SRC>_<Table>. A table Person.Person coming from the AW connection would therefor be called AW_Person_Person whereas a table called dbo.dataarea from the AX connection would be named AX_dataarea.

Again, your naming schema might be different from this so you may have to adjust the logic that reads the tablenames.

First, we need to point Biml to our existing staging database, which we will do by just creating a static environment definition:

 <Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        	<OleDbConnection Name="Source" ConnectionString="Provider=SQLNCLI11;Server=.;Initial Catalog=MyExistingStagingDatabase;Integrated Security=SSPI;" />  
	</Connections>
</Biml>

Now, all we need is a collection of all tables matching the prefix of a given connection, which we will achieve using the ImportDB method (which allows SQL like syntax for filtering).

For each member of that collection, we follow some simple rules:

– Our table’s original name is the name of the table in the staging area without our connectionname prefix
– If our tablename still includes an underscore, we will split the name and assign the table- and schemaname respectively. Otherwise, our schema will be DBO.
– Create a DELETE statement towards our metadata store
– Create an INSERT statement towards our metadata store

Notice, that this will be called using CallBimlScript by accepting the source connection as a property:

 <#@ template language="VB" #>
<#@ property name="SRC" type="string" #>
<# Dim TableName as string
   Dim SchemaName as string
   Dim SourceConnection as AstDbConnectionNode  = RootNode.Connections("Source") 
   Dim SrcTableResults as ImportResults = SourceConnection.ImportDB(nothing,SRC + "_%") 
   for each tbl as asttablenode in srctableresults.tablenodes
   tablename = tbl.name.substring(src.length+1)
   if tablename.indexof("_") < 0 then
   	SchemaName = "DBO"
	else
	SchemaName = tablename.substring(0,tablename.indexof("_"))
	tablename = tablename.substring(tablename.indexof("_")+1)
   end if #>
   DELETE FROM MyBimlMeta_Tables WHERE Connectionname = '<#= SRC #>' and SchemaName = '<#= schemaname #>' and Tablename = '<#= tablename #>'
   INSERT INTO MyBimlMeta_Tables (Connectionname,Schemaname,Tablename,Columns) values ('<#= src #>','<#= schemaname #>','<#= Tablename #>','<#= tbl.getcolumnlist #>')
<#next#>

As we only plan to run this script once, instead of creating a package for it, we will just dump out the SQL script into a .sql file which can then be run in SSMS:

<#@ template language="VB" #>
<# System.IO.File.WriteAllText("c:\Temp\MyBimlTables_AW.sql",CallBimlScript("02_BuildSQL.biml","AW")) #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd"/>

This will generate all the records required for our metadata model!

Any questions or comments? We’d love to hear from you at !

Further Information on Biml in English can be found on our English Biml Page.

Happy Biml’ing!