Using metadata and Biml to control your deployment

Using metadata and Biml to control your deployment
Diesen Artikel finden Sie hier auch in deutscher Sprache.

Who said, that metadata can only be used to describe where your data is coming from? In this example, we’ll work with the following scenario: You are running sites in three different regions (USA, Asia and Europe) which all have a similar setup with a comparable source database (which might still differ a bit, for example due to different collations). You’re trying to establish a staging area in each region that follows the same rules and contains the same table structures etc. One option might be to just use package configurations but we’ll create custom packages for each region based on our metadata.

Our sample data will all run in the same instance – if you have the possibility to test drive this demo across multiple instances, feel free to modify the metadata accordingly.

In preparation, please create 4 new databases:

MyBiml_MultiTarget – this database will hold our metadata
MyBiml_MultiTarget_USA
MyBiml_MultiTarget_ASIA
MyBiml_MultiTarget_EUROPE

As a next step, please run these scripts to create the metadata model:

USE [MyBiml_MultiTarget]
GO
CREATE TABLE [dbo].[MyBimlMeta_Connections](
	[TargetName] [nvarchar](50) NULL,
	[ConnectionName] [nvarchar](50) NULL,
	[ConnectionServer] [nvarchar](50) NULL,
	[ConnectionDatabase] [nvarchar](50) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[MyBimlMeta_Tables](
	[ConnectionName] [nvarchar](50) NULL,
	[Tablename] [nvarchar](50) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[MyBimlMeta_Targets](
	[TargetName] [nvarchar](50) NULL,
	[TargetServer] [nvarchar](50) NULL,
	[TargetDatabase] [nvarchar](50) NULL,
	[IsActive] [bit] NULL
) ON [PRIMARY]
GO
CREATE VIEW [dbo].[vMyBimlMeta_Connections]
AS
	SELECT targets.[TargetName]
		, [ConnectionName]+'_'+targets.Targetname AS [ConnectionName]
		, [ConnectionName] AS BaseConnection
		, [ConnectionServer]
		, [ConnectionDatabase]
		, 'Source' AS Type
	FROM [MyBimlMeta_Connections] AS Conn
	INNER JOIN [dbo].[MyBimlMeta_Targets] AS Targets
		ON targets.[TargetName] = conn.[TargetName]
	WHERE ISNULL(isactive, 0) = 1
	UNION ALL
	SELECT TargetName
		, 'Target_'+targetname AS Connectionname
		, targetname AS BaseConnection
		, TargetServer
		, TargetDatabase
		, 'Target' AS Type
	FROM dbo.MyBimlMeta_Targets
	WHERE ISNULL(isactive, 0) = 1
GO
CREATE VIEW [dbo].[vMyBimlMeta_Tables]
AS
	SELECT Conn.ConnectionName
		, TableName
	FROM dbo.vMyBimlMeta_Connections AS Conn
	INNER JOIN dbo.MyBimlMeta_Tables AS tbl
		ON Conn.BaseConnection = tbl.ConnectionName
GO
INSERT [dbo].[MyBimlMeta_Connections] ([TargetName], [ConnectionName], [ConnectionServer], [ConnectionDatabase]) VALUES (N'ASIA', N'AW', N'localhost', N'AdventureWorks2014')
GO
INSERT [dbo].[MyBimlMeta_Connections] ([TargetName], [ConnectionName], [ConnectionServer], [ConnectionDatabase]) VALUES (N'USA', N'AW', N'localhost', N'AdventureWorks2014')
GO
INSERT [dbo].[MyBimlMeta_Connections] ([TargetName], [ConnectionName], [ConnectionServer], [ConnectionDatabase]) VALUES (N'EUROPE', N'AW', N'localhost', N'AdventureWorks2014')
GO
INSERT [dbo].[MyBimlMeta_Tables] ([ConnectionName], [Tablename]) VALUES (N'AW', N'Person')
GO
INSERT [dbo].[MyBimlMeta_Tables] ([ConnectionName], [Tablename]) VALUES (N'AW', N'PersonPhone')
GO
INSERT [dbo].[MyBimlMeta_Targets] ([TargetName], [TargetServer], [TargetDatabase], [IsActive]) VALUES (N'USA', N'localhost', N'MyBiml_MultiTarget_USA', 1)
GO
INSERT [dbo].[MyBimlMeta_Targets] ([TargetName], [TargetServer], [TargetDatabase], [IsActive]) VALUES (N'ASIA', N'localhost', N'MyBiml_MultiTarget_ASIA', 1)
GO
INSERT [dbo].[MyBimlMeta_Targets] ([TargetName], [TargetServer], [TargetDatabase], [IsActive]) VALUES (N'EUROPE', N'localhost', N'MyBiml_MultiTarget_EUROPE', 1)
GO 

Also, please create a new Biml solution and add a code file with the following content. Compared to our previous blog posts, there is nothing new here, so we won’t spend any time explaining the actual code.

 
Imports Varigence.Biml.CoreLowerer.SchemaManagement
Imports Varigence.Biml.Extensions
Imports Varigence.Languages.Biml
Imports Varigence.Languages.Biml.Connection
Imports System.Data
Imports System.Collections.Generic
Public Class BF
	Public Shared ControlDB As String = "MyBiml_MultiTarget"
	Public Shared ControlServer As String = "Localhost"
	
	Public Shared Function GetConnectionString (Server As String, DB As String) As String
	 Return "Provider=SQLNCLI11;Server=" & server & ";Initial Catalog=" & DB & ";Integrated Security=SSPI;"
	End Function
 
	Public Shared Function GetNonEmptyList (Conn As AstDbConnectionNode, SQL As String) As List(Of String)
		Dim tmplist As New List(Of String)
		If SQL.Contains(" ") = 0 Then sql = "select * from " + sql
		Dim DT As DataTable  = ExternalDataAccess.GetDataTable(Conn.ConnectionString, SQL)
		For Each dr As datarow In dt.rows
			tmplist.add(dr.item(0).ToString())
		Next			
		If tmplist.count = 0 Then tmplist.add ("NONEMPTYFILLER")
		Return tmplist		
	End Function
	Public Shared Function DefaultImportOptions
		Return ImportOptions.ExcludeIdentity Or ImportOptions.ExcludePrimaryKey  Or ImportOptions.ExcludeUniqueKey Or ImportOptions.ExcludeColumnDefault _ 
			Or ImportOptions.ExcludeIndex Or ImportOptions.ExcludeCheckConstraint Or ImportOptions.ExcludeForeignKey
	End Function 	
End Class

Based on that metadata, we will start by creating our connections in Biml by simply looping over the data in SQL.

In addition to required attributes like Name and ConnectionString, we’ll add some annotations. By declaring a "Type" for example, we can easily distinct between source and target systems later on.

<#@ template language="VB" tier="1" #>
<#@ code file="../code/BimlFunctions.vb" #>
<#@ import namespace="System.Data"  #>
<#@ import namespace="BF"  #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
	<Connections>
		<OleDbConnection Name="Control" ConnectionString="<#= GetConnectionString(ControlServer, ControlDB) #>"/>
		<# For Each dr As datarow In  ExternalDataAccess.GetDataTable( GetConnectionString(ControlServer, ControlDB), "Select * from vMyBimlMeta_Connections").rows #>
		<OleDbConnection Name="<#=dr("ConnectionName") #>" ConnectionString="<#= GetConnectionString(dr("ConnectionServer"),dr("ConnectionDatabase")) #>">
			<Annotations>
				<Annotation AnnotationType="Tag" Tag="Type"><#=dr("Type") #></Annotation>
				<Annotation AnnotationType="Tag" Tag="BaseConnection"><#=dr("BaseConnection") #></Annotation>
				<Annotation AnnotationType="Tag" Tag="DatabaseName"><#=dr("ConnectionDatabase") #></Annotation>
				<Annotation AnnotationType="Tag" Tag="TargetName"><#=dr("TargetName") #></Annotation>
        	</Annotations>
		</OleDbConnection>
		<# Next	#>
	</Connections>
</Biml> 

Our next step is to create a database node for each target and following that a schema node for each database in Biml:

<#@ template language="VB" tier="2" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
		<Databases>
		<# for each target as AstConnectionBaseNode in rootnode.connections.where(function(c) c.GetTag("Type") = "Target") #>
			<Database Name="<#= target.GetTag("DatabaseName") #>" ConnectionName="<#= target.name #>">
				<Annotations>
					<Annotation AnnotationType="Tag" Tag="TargetName"><#= target.getTag("TargetName") #></Annotation>
            	</Annotations>
			</Database>
		<# next #>
	</Databases>
</Biml>
 
 <#@ template language="VB" tier="3" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
	<Schemas>
	<# for each db as AstDatabasenode in rootnode.databases#>
		<Schema Name="dbo" DatabaseName="<#=db.name #>">
			<Annotations>
				<Annotation AnnotationType="Tag" Tag="TargetName"><#= db.getTag("TargetName") #></Annotation>
            </Annotations>
		</Schema>
	<# next #>
	</Schemas>
</Biml>

Now that we’ve defined connections, databases and schemas we still need to add our table metadata.

We’re going to do that by looping across all our databases marked as a source in Biml, retrieving the list of required tables from SQL (located in View vMyBimlMeta_Tables) and creating a table tag for each table which will also reference back to the corresponding target system. That also allows us to use the same table names multiple times. Again, we’ll store some additional data in annotations.

 <#@ template language="VB" tier="4" #>
<#@ code file="../code/BimlFunctions.vb" #>
<#@ import namespace="BF"  #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Tables>
        <# for each SrcDB as AstConnectionBaseNode in rootnode.connections.where(function(c) c.GetTag("Type") = "Source") 
		   for each Tbl as asttablenode in SrcDB.GetDatabaseSchema(nothing,getnonemptylist(rootnode.connections("Control"),"Select Tablename from [vMyBimlMeta_Tables] where ConnectionName = '" & srcdb.name & "'"),defaultimportoptions).tablenodes #>
        <Table Name="<#= srcDb.GetTag("BaseConnection") #>_<#= tbl.schema.name #>_<#= tbl.name #>" SchemaName="<#= rootnode.schemas.where(function(c) c.GetTag("TargetName") = srcDb.GetTag("TargetName") ).First.scopedname #>">
            <Columns>
                <#= tbl.columns.getbiml #>
            </Columns>
            <Annotations>
                <Annotation AnnotationType="Tag" Tag="TableName"><#= tbl.scopedname #></Annotation>
                 <Annotation AnnotationType="Tag" Tag="TargetName"><#=SrcDB.GetTag("TargetName") #></Annotation>
				 <Annotation AnnotationType="Tag" Tag="SrcName"><#=SrcDB.Name #></Annotation>
            </Annotations>
        </Table>
        <#  next
		   next  #>
    </Tables>
</Biml>

Having created all metadata required by Biml, we can create one package for each target which will make use of a CREATE Statement for each table that we’ll need there:

 <#@ template language="VB" tier="5" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
	<Packages>
		<# for each target as AstConnectionBaseNode in rootnode.connections.where(function(c) c.GetTag("Type") = "Target") #>
			<Package Name="Create_Staging_<#=target.gettag("TargetName") #>" PackageSubpath="<#=target.gettag("TargetName") #>">
				<Tasks>
					<# for each tbl as AstTableNode in rootnode.tables.where(function(c) c.GetTag("TargetName").Trim = target.gettag("TargetName").trim) #>
						<ExecuteSQL Name="Create <#=tbl.Name#>" ConnectionName="<#= target.name #>">
							<DirectInput>
								<#=tbl.GetDropAndCreateDdl()#>
							</DirectInput>
						</ExecuteSQL>
					<# next #>
                </Tasks>
			</Package>
		<# next #>
    </Packages>
</Biml>

Our last step is simply another Biml file which will generate another package per target system to populate our tables:

 <#@ template language="VB" tier="6" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
	<Packages>
		<# for each target as AstConnectionBaseNode in rootnode.connections.where(function(c) c.GetTag("Type") = "Target") #>
			<Package Name="Populate_Staging_<#=target.gettag("TargetName") #>" PackageSubpath="<#=target.gettag("TargetName") #>">
				<Tasks>
					<# for each tbl as AstTableNode in rootnode.tables.where(function(c) c.GetTag("TargetName").Trim = target.gettag("TargetName").trim) #>
					<Container Name="Transfer  <#=tbl.Name#>" ConstraintMode="Linear">
                    <Tasks>
                        <ExecuteSQL Name="Truncate" ConnectionName="<#= target #>">
                            <DirectInput>truncate table <#=tbl.ScopedName#></DirectInput>
                        </ExecuteSQL>
                       	<Dataflow Name="Copy <#=tbl.Name#>">
                           	<Transformations>
                               	<OleDbSource Name="Get Rows" ConnectionName="<#= tbl.GetTag("SrcName").trim #>">
                                   	<DirectInput>SELECT <#= tbl.GetColumnList #> from <#= tbl.GetTag("TableName") #></DirectInput>
                               	</OleDbSource>
                           		<OleDbDestination Name="Set Rows" ConnectionName="<#= target #>">
                               	    <TableOutput TableName="<#=tbl.ScopedName#>" />
                    	   	     </OleDbDestination>
                     	    </Transformations>
                        </Dataflow>
                  	  </Tasks>
               		</Container>
					<# next #>
                </Tasks>
			</Package>
		<# next #>
    </Packages>
</Biml>

This leaves us with a solution where we can not only control which data to extract, but also where to load it to. Of course, this approach wouldn’t just work to manage multiple regions but could for example also be used to roll out towards a TEST and/or PROD environment.

Any questions or comments? We’d love to hear from you at biml@solisyon.de!

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

Happy Biml’ing!

Facebook