This blog post has also been published in English.
Sie haben Teil 1 gelesen, fanden es ganz nett, aber nicht über die Maßen spannend? Dann versuchen wir das noch einmal!
Die größten Schwächen der ersten Lösung waren:
– Sie unterstützt nur eine Datenquelle
– Es existierte kein Spaltenfilter, so dass Tabellen immer komplett übertragen wurden
Um diese beiden Themen zu adressieren, müssen wir unseren Stammdaten-Layer zunächst etwas erweitern. Dies tun wir in einer neuen Datenbank namens „MyBiml_Destination“.
Auch hier erstellen wir zunächst die Steuertabellen und befüllen sie mit einigen Musterdaten:
USE [MyBiml_Destination] GO CREATE TABLE [dbo].[MyBimlMeta_Connections]( [ConnectionName] [nvarchar](50) NULL, [ServerName] [nvarchar](50) NULL, [DatabaseName] [nvarchar](50) NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[MyBimlMeta_Tables]( [ConnectionName] [nvarchar](50) NULL, [SchemaName] [nvarchar](50) NULL, [TableName] [nvarchar](50) NULL, [Columns] [nvarchar](max) NULL, [Disabled] [int] NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO INSERT INTO [MyBimlMeta_Connections] values ('AW','localhost','AdventureWorks2014') INSERT INTO [MyBimlMeta_Tables] values ('AW','Person','Person','*',0) INSERT INTO [MyBimlMeta_Tables] values ('AW','Person','PersonPhone','PhoneNumber',0)
Es werden somit alle Spalten der Tabelle Person.Person sowie PhoneNumber aus Person.PersonPhone übertragen.
Auch unser Codefile benötigt einige kleinere Anpassungen:
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 TargetDB As String = "MyBiml_Destination" Public Shared TargetConnString As String = "Provider=SQLNCLI11;Server=localhost;Initial Catalog="+ TargetDB + ";Integrated Security=SSPI;" Public Shared columns As String Public Shared tblname As String Public Shared sql As String Public Shared ImportColumns As New List(Of String) Public Shared Function GetTableID (Conn As String, Schema As String, Tbl As String) As String Dim tblname As String If Schema.toupper = "DBO" Then tblname = (conn+"_" + Tbl).toupper Else tblname = (conn+"_" +Schema+"_" +tbl).toupper End If Return tblname End Function Public Shared Function GetNonEmptyList (Conn As AstDbConnectionNode, SQL As String) As List(Of String) Dim tmplist As New List(Of String) 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 ("NONEMPTYSPACER") 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
Jetzt kommt der spannende Teil – die Erweiterungen unserer Biml-Dateien:
01_Environment.biml
Statt nur eine statische Quelle zu definieren, durchlaufen wir unsere Connections-Tabelle und markieren alle Einträge mit einer Annotation IsSource=True.
Hierdurch können wir, gesteuert in unseren Metadaten, mit beliebig vielen Quell-Verbindungen arbeiten.
<#@ template language="VB" #> <#@ import namespace="System.Data" #> <#@ code file="../code/BimlFunctions.vb" #> <#@ import namespace="BF" #> <Biml xmlns="http://schemas.varigence.com/biml.xsd"> <Connections> <OleDbConnection Name="Target" ConnectionString="<#= TargetConnString #>" > <Annotations> <Annotation Tag="DBString"><#= TargetDB #>.dbo.</Annotation> </Annotations> </OleDbConnection> <# for each row as datarow in ExternalDataAccess.GetDataTable(TargetConnString, "SELECT ConnectionName, ServerName, DatabaseName FROM [MyBimlMeta_Connections] where connectionname <> 'target' order by connectionname").rows #> <OleDbConnection Name="<#= row.item(0) #>" ConnectionString="Provider=SQLNCLI11;Server=<#= row.item(1) #>;Initial Catalog=<#= row.item(2) #>;Integrated Security=SSPI;"> <Annotations> <Annotation Tag="IsSource">True</Annotation> </Annotations> </OleDbConnection> <#next #> </Connections> <Databases> <Database Name="<#= TargetDB #>" ConnectionName="Target" /> </Databases> <Schemas> <Schema Name="dbo" DatabaseName="<#= TargetDB #>" /> </Schemas> </Biml>
02_BuildMeta.biml
In dieser Datei gibt es die größten Änderungen, da wir nun:
– Alle Connections, die als Quelle gekennzeichnet sind, durchlaufen
– Für jede dieser Verbindungen das Schema abrufen, gefiltert auf die in MyBimlMeta_Tables für die jeweilige Verbindung definierten Tabellen
– Aus der Quelldatenbank alle Spalten gemäß der in MyBimlMeta_Tables für die Tabelle definieren Spalten (Columnns) abrufen
– Für jede Tabelle einen Table-Tag sowie für jede zurückgegebene Spalte einen Column-Tag anlegen
Wir erhalten somit nur die vorgegebenen Tabellen und Spalten – es sei denn, die Spalten wurden als „*“ definiert, woraufhin weiterhin alle Spalten der Quelltabelle abgerufen würden.
<#@ template language="VB" tier="2" #> <#@ import namespace="System.Data" #> <#@ code file="../code/BimlFunctions.vb" #> <#@ import namespace="BF" #> <Biml xmlns="http://schemas.varigence.com/biml.xsd"> <Tables> <# for each SrcConn as AstDbConnectionNode in RootNode.Connections.where(function(c) c.getTag("IsSource") = "True") sql = "SELECT [TableName] FROM [MyBimlMeta_Tables] where ConnectionName = '" + srcconn.name + "' and isnull(disabled,0) = 0" dim importResult as Importresults = srcconn.GetDatabaseSchema(nothing,GetNonEmptyList(rootnode.connections("Target"),sql),DefaultImportOptions) for each table as asttablenode in importResult.TableNodes tblname = GetTableID(srcconn.name,table.Schema.Name,table.Name) #> <Table Name="<#= tblname#>" SchemaName="<#= rootnode.schemas(0).Scopedname #>"> <Columns> <# sql = "SELECT Columns FROM [MyBimlMeta_Tables] where ConnectionName = '" + srcconn.name + "' and tablename = '" + table.name + "' and schemaname = '" + table.Schema.Name + "' and isnull(disabled,0) = 0" columns = ExternalDataAccess.GetDataTable(TargetConnString, sql).rows(0).item(0) ImportColumns.clear for each dc as datacolumn in ExternalDataAccess.GetDataTable(srcconn.connectionstring,"SELECT TOP 0 " + columns + " from " + table.SchemaQualifiedName).Columns importcolumns.add ( dc.Caption.ToUpper ) next for each column as AstTableColumnBaseNode in table.Columns.where(function(c) importcolumns.IndexOf(c.name.toupper) <> -1) #> <#=column.GetBiml()#> <# next #> </Columns> <Annotations> <Annotation AnnotationType="Tag" Tag="SourceSchemaQualifiedName"> <#=table.SchemaQualifiedName#> </Annotation> <Annotation AnnotationType="Tag" Tag="Connection"> <#=srcconn.name#> </Annotation> </Annotations> </Table> <# next next#> </Tables> </Biml>
03_Create_Staging.biml
Mit Ausnahme des SubPath gibt es hier keine Änderungen im Vergleich zu unserem Teil 1.
<#@ template tier="2" language="VB" #> <Biml xmlns="http://schemas.varigence.com/biml.xsd"> <Packages> <Package Name="01_CreateStaging" ConstraintMode="Parallel" ProtectionLevel="EncryptSensitiveWithUserKey" PackageSubpath="MyBiml"> <Tasks> <# for each table as asttablenode in RootNode.Tables #> <ExecuteSQL Name="Create <#=table.Name#>" ConnectionName="Target"> <DirectInput> <#=table.GetDropAndCreateDdl()#> </DirectInput> </ExecuteSQL> <# next #> </Tasks> </Package> </Packages> </Biml>
04_Populate_Staging.biml
Auch hier gibt es keine wesentlichen Änderungen. Die einzige größere Anpassung ist, dass wir in jedem Datenfluss-Task nun dynamisch auf die Quell-Verbindung verweisen, statt auf eine statische „Source“.
<#@ template tier="4" language="VB" #> <Biml xmlns="http://schemas.varigence.com/biml.xsd"> <Packages> <Package Name="02_Populate Tables" ConstraintMode="Parallel" ProtectionLevel="EncryptSensitiveWithUserKey" PackageSubpath="MyBiml"> <Tasks> <# for each table as asttablenode in rootnode.tables #> <Container Name="Transfer <#=table.Name#>" ConstraintMode="Linear"> <Tasks> <ExecuteSQL Name="Truncate <#=table.Name#>" ConnectionName="Target"> <DirectInput>truncate table <#=table.name#></DirectInput> </ExecuteSQL> <Dataflow Name="Copy <#=table.Name#>"> <Transformations> <OleDbSource Name="Retrieve Rows" ConnectionName="<#=table.GetTag("Connection").trim#>"> <DirectInput>SELECT <#=table.GetColumnList()#> FROM <#=table.GetTag("SourceSchemaQualifiedName")#></DirectInput> </OleDbSource> <OleDbDestination Name="Load Rows" ConnectionName="Target"> <TableOutput TableName="<#=table.ScopedName#>" /> </OleDbDestination> </Transformations> </Dataflow> </Tasks> </Container> <# next #> </Tasks> </Package> </Packages> </Biml>
Verglichen mit unserem Teil 1 haben wir mit wenig Mehraufwand deutlich mehr Flexibilität erreicht.
Und erneut ist dies erst der Anfang. Man könnte die Lösung weiter ergänzen und verbessern, zum Beispiel durch das Hinzufügen von:
– CallBimlScript oder Include Files für bessere Lesbarkeit
– Inkrementellem Erstellen der Staging Tabellen
– „Aufräumen“ alter Tabellen
– Index Management
– Metadaten für Unterpakete um nicht immer Tabellen auf einmal zu beladen UND SO VIELES MEHR :)
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!