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 !
Further Information on Biml in English can be found on our English Biml Page.
Happy Biml’ing!