This blog post has also been published in English.
Wer sagt eigentlich, dass man mittels Metadaten nur Datenquellen beschreiben kann? In diesem Beispiel gehen wir von folgendem Szenario aus: Es gibt in 3 Regionen (USA, Asien, Europa) jeweils ein ähnliches Systemsetup mit einer gleichartigen Quelldatenbank und dem Ziel jeweils identische Staging-Umgebungen zu betreiben. Die Quelldatenbanken können sich in diesem Szenario zwischen den Standorten z.B. durch unterschiedliche Collations leicht unterscheiden. Eine Möglichkeit wäre das Arbeiten mit entsprechenden Verbindungs-Konfigurationen. Wir erstellen aber mittels Metadaten einfach entsprechende Pakete je Region.
In unserem Fall liegen alle Umgebungen in der gleichen Datenbankinstanz – falls Ihnen verschiedene Instanzen zur Verfügung stehen, so können Sie dies gerne bei den Metadaten berücksichtigen.
Zur Vorbereitung legen Sie bitte die folgenden 4 Datenbanken an:
MyBiml_MultiTarget – Diese Datenbank enthält unsere Metadaten
MyBiml_MultiTarget_USA
MyBiml_MultiTarget_ASIA
MyBiml_MultiTarget_EUROPE
Dann führen Sie zur Vorbereitung bitte die folgenden Scripte aus:
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
Zusätzlich legen Sie bitte in einer neuen Biml Solution ein neues Codefile mit dem nachfolgenden Inhalt an. Da diese Datei inhaltlich im Vergleich zu den vorhergehenden Blog-Beiträgen keine Neuerungen birgt, gehen wir auf den Inhalt hier nicht näher ein.
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
Basierend auf unseren Metadaten legen wir uns nun mittels einem Loop die Verbindungen in Biml an.
Hierbei geben wir den Verbindungen, neben den ohnehin erforderlichen Attributen Name und ConnectionString, mittels Annotations noch einige Zusatzinformationen mit. Über die Information “Type” können wir später beispielsweise einfach zwischen Quell- und Zieldatenbanken unterscheiden.
<#@ 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>
Im nächsten Schritt legen wir für jede Zieldatenbank (Type = Target) einen Database- und darauf basierend wiederum einen Schema-Eintrag in Biml an:
<#@ 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>
Jetzt, da wir Verbindungen, Datenbanken und Schemata definiert haben, benötigen wir noch die entsprechenden Metadaten für unsere Tabellen.
Hierfür nutzen wir den bekannten Weg eines Loops über die Quelldatenbanken, ermitteln (aus der View vMyBimlMeta_Tables) die für diese Datenbank bzw. diesen System-Typ definierten Tabellen und erzeugen auch hierfür jeweils einen Eintrag mit Verweis auf das entsprechende Ziel-Schema. Hierdurch stellt es auch kein Problem dar, dass Tabellen teilweise auf den ersten Blick doppelt erscheinen. Auch hier nutzen wir einige Annotations zur Weitergabe von zusätzlichen Informationen.
<#@ 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>
Da nun alle Metadaten vorliegen können wir für jedes Zielsystem ein entsprechendes Paket erstellen, welches wiederum für alle Tabellen dieses Systems entsprechende CREATE Statements zur Erzeugung der Zieltabellen generiert:
<#@ 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>
Der letzte Schritt ist dann lediglich noch eine Datei, welche wiederum für jedes Zielsystem ein Paket zur Befüllung der Tabellen erzeugt:
<#@ 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>
Somit haben wir eine Lösung, in der wir nicht nur mittels Metadaten steuern können, welche Daten wir abholen sondern auch, wohin diese geladen werden sollen. Dieser Ansatz funktioniert so natürlich nicht nur für regional unterschiedliche Systeme sondern könnte auch als Grundlage dienen, um Änderungen in ein TEST und ein PROD System auszurollen.
Haben Sie hierzu Fragen oder Anmerkungen? Wir freuen uns auf Ihren Input unter !
Weitere Informationen zu Biml, einschließlich Terminen und Blog Beiträgen finden Sie auch auf unserer Biml Seite.
Viel Spaß beim Biml’n!