Housekeeping with Biml

Housekeeping with Biml
Diesen Artikel finden Sie hier auch in deutscher Sprache.

So you’ve created that perfect Biml solution to build your staging area. It reads your source database structure, maybe you even built a meta-data based management layer around it to control which tables and columns you actually want to bring into the warehouse. But as what you do is work in progress, you potentially end up with some unwanted stepchildren, as there might be tables that are not required anymore or have actually been removed from the source.

Now I am fully aware, that simply dropping those tables in the staging environment is not always what you want – still it might come in quite handy, especially in the early stages of your solution.

One easy way of doing so is flagging all your tables that have been created using Biml and then, when re-building your staging tables, look for all flagged but unused tables.

To do so, just add an extended property to your tables using sp_addextendedproperty and then filter for all tables that have your extended property and are not part of your Biml-tables anymore.

How does that look?

	<#@ template tier="2" language="VB" #>
	<#@ import namespace="System.Data" #>
	<Biml xmlns="">
	        <Package Name="01_CreateStaging" ConstraintMode="Parallel">
	                <# for each table as asttablenode in RootNode.Tables  #>
	                <ExecuteSQL Name="Create <#=table.Name#>" ConnectionName="Target">
							EXEC sys.sp_addextendedproperty @name=N'BimlGenerated', @value=N'True' , @level0type=N'SCHEMA',@level0name=N'<#= table.schema #>', @level1type=N'TABLE',@level1name=N'<#= #>'
	                <# next #>
	                <# dim targetConnection as AstDbConnectionNode  = RootNode.Connections("Target") 
					   dim epsql as string = "SELECT  objname FROM fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', default, Null, NULL) where name = 'BimlGenerated' and value='True' and objtype='Table'"
					Dim DT as DataTable  = ExternalDataAccess.GetDataTable(targetConnection.ConnectionString, epsql)
	  				for each DR as datarow in dt.rows 
					dim tbl as string = dr.item(0) 
					if Rootnode.tables(tbl) is nothing then  #>
	                <ExecuteSQL  Name="Remove <#= tbl #>" ConnectionName="Target">
	                    <DirectInput>IF EXISTS (SELECT * from sys.objects WHERE object_id = OBJECT_ID(N'[dbo].<#= tbl #>') AND type IN (N'U'))
							DROP TABLE [dbo].<#= tbl #>
	                <#  end if
						   next #>
By just adding one line to the creation-block and adding another block that will filter on all affected tables and then check against RootNode.Tables, we will ensure that a table that was created by Biml but is not part of your solution anymore will be dropped.

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!