[spb_text_block pb_margin_bottom=”no” pb_border_bottom=”no” width=”1/1″ el_position=”first last”]
This blog post has also been published in English.
Einer der offensichtlichsten Anwendungsfälle für Biml ist die Automatisierung einfacher Datenbeladungen. Um hiervon jedoch wirklich zu profitieren, bedarf es einer Metadaten-Schicht über die man Biml “mitteilt”, welche Daten überhaupt relevant sind.
In diesem ersten Teil beschränken wir uns in einem einfachen Ansatz auf nur eine statisch definierte Datenquelle und nutzen auch nur Tabellennamen als Metadaten. Das klingt, richtigerweise, nach einem “Alles oder nichts”-Modell, vermittelt aber einen guten ersten Eindruck wie man die Metadaten an Biml weitergeben kann.
In einem Folgeartikel widmen wir uns dann der Erweiterung dieser Lösung um mehrere Datenquellen sowie die Definition von Quellspalten, was effektiv die Automatisierung jeglicher “Kopiere diese Daten von hier nach dort”-Aufgaben ermöglicht.
Aber eines nach dem anderen – halten wir es zunächst etwas einfacher.
Als Basis benötigen wir eine Datenquelle (wir verwenden hier AdventureWorks2014) sowie eine Ziel-Datenbank. Letztere nennen wir “MySimpleBiml_Destination”, welche nur eine einzige Steuertabelle enthalten wird. Diese wiederum lässt sich mit folgendem T-SQL Statement erstellen und beispielhaft befüllen:
CREATE TABLE [dbo].[MyBimlMeta_Tables]( [TableName] [nvarchar](50) NULL ) ON [PRIMARY] GO INSERT INTO [dbo].[MyBimlMeta_Tables] SELECT NAME FROM AdventureWorks2014.dbo.sysobjects where name like 'Sales%' and type = 'U'
Das obenstehende Script befüllt die Steuertabelle einfach mit allen Tabellen aus AdventureWorks deren Name mit “Sales” beginnt.
Aus Gründen der Lesbarkeit und Wiederverwendbarkeit teilen wir unseren Quellcode auf vier logische Biml-Dateien sowie eine VB-Code Datei auf.
Die Code-Datei enthält einige Variablen und Funktionen, die es uns etwas erleichtern. Da es sich hier um relativen einfachen VB Code handelt gehen wir an dieser Stelle nicht nähere darauf ein. Sollte es dennoch Fragen geben, so melden Sie sich gerne!
Hier der VB-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 TargetDB As String = "MySimpleBiml_Destination"
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
Deutlich wichtiger und spannender hingegen sind unsere Biml-Dateien:
01_Environment.biml
In dieser Datei definieren wir lediglich die Verbindungen zur AdventureWorks-Datenbank sowie zu unserer Staging Umgebung.
<#@ template language="VB" tier="1" #> <#@ code file="../code/BimlFunctions.vb" #> <Biml xmlns="http://schemas.varigence.com/biml.xsd"> <Connections> <OleDbConnection Name="Target" ConnectionString="Provider=SQLNCLI11;Server=.;Initial Catalog=<#= BF.TargetDB #>;Integrated Security=SSPI;" /> <OleDbConnection Name="Source" ConnectionString="Provider=SQLNCLI11;Server=.;Initial Catalog=AdventureWorks2014;Integrated Security=SSPI;"/> </Connections> <Databases> <Database Name="<#= BF.TargetDB #>" ConnectionName="Target" /> </Databases> <Schemas> <Schema Name="dbo" DatabaseName="<#= BF.TargetDB #>" /> </Schemas> </Biml>
02_BuildMeta.biml
In dieser Datei bauen wir unsere Tabellen-Metadaten auf. Hierfür loopen wir zunächst durch unsere MyBimlMeta_Tables-Tabelle, holen uns die Spaltendefinitionen der hinterlegten Tabellen aus AdventureWorks und erstellen einen entsprechenden Table-Tag innerhalb unseres Biml-Codes. Da wir auf Spalten keinerlei Einschränkungen setzen, können wir uns einfach der GetDatabaseSchema-Funktion bedienen, erneut durch ihr Ergebnis loopen und das Ergebnis mittels GetBiml zurückgeben (GetDatabaseSchema akzeptiert 3 Parameter, wobei diese auch alle oder teilweise leer bzw. NULL sein dürfen: Eine zu filternde Liste von Schemata, eine zu filternde Liste von Tabellen sowie ImportOptions mit denen beispielsweise Indices etc. ausgeschlossen werden können – wir verwenden hier eine Reihe von sinnvollen Einschränkungen).
Zusätzlich setzen wir eine Annotation – falls Sie diese bisher nicht verwendet haben: Diese sind quasi das Pendant zu einer erweiterten Tabelleneigenschaft an einem Biml-Objekt – um das SELECT Statement zu hinterlegen, welches wir später benötigen um effektiv die Daten aus AdventureWorks abzufragen und unsere Metadaten zu befüllen.
<#@ template language="VB" tier="2" #>
<#@ code file="../code/BimlFunctions.vb" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Tables>
<# dim AW as astdbconnectionnode = RootNode.Connections("Source")
dim importResult as Importresults = AW.GetDatabaseSchema(nothing,BF.GetNonEmptyList(RootNode.Connections("Target"),"MyBimlMeta_Tables"), BF.DefaultImportOptions)
for each table as asttablenode in importResult.TableNodes #>
<Table Name="AW_<#=table.Schema.Name#>_<#=table.Name#>" SchemaName="<#= BF.TargetDB #>.dbo">
<Columns>
<#= table.columns.getbiml #>
</Columns>
<Annotations>
<Annotation AnnotationType="Tag" Tag="SQLSelect">
SELECT <#=table.GetColumnList()#> FROM <#=table.SchemaQualifiedName #>
</Annotation>
</Annotations>
</Table>
<# next #>
</Tables>
</Biml>
03_Create_Staging.biml
Nachdem wir unsere Metadaten definiert haben, folgt das Anlegen der entsprechenden Tabellen in der Staging Datenbank. Hierfür erstellen wir ein neues SSIS Paket, loopen über die Biml-Metadaten (welche im Objekt Rootnode.Tables liegen) und erzeugen je Tabelle (welche durch ein AstTableNode Objekt dargestellt wird) einen ExecuteSQL Task in dem wir auf Basis der GetDropAndCreateDDL Funktion ein entsprechendes Statement ausgeben. Das war es schon.
<#@ template tier="3" language="VB" #> <Biml xmlns="http://schemas.varigence.com/biml.xsd"> <Packages> <Package Name="01_CreateStaging" ConstraintMode="Parallel" PackageSubpath="MySimpleBiml" ProtectionLevel="DontSaveSensitive"> <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>
Bevor wir zum nächsten Schritt kommen, ist es erforderlich dieses Paket zu erstellen und auszuführen, damit die zu befüllenden Tabellen auch angelegt sind/werden.
04_Populate_Staging.biml
Jetzt, da wir alle Zieltabellen haben, erzeugen wir ein weiteres SSIS Paket und loopen erneut über die Metadaten – nur dass wir diese nun nicht erstellen sondern befüllen. Hierfür erzeugen wir je Tabelle einen Sequenz-Container mit wiederum einem Truncate- sowie einem Datenfluss-Task.
<#@ template tier="4" language="VB" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Packages>
<Package Name="02_Populate Tables" ConstraintMode="Parallel" ProtectionLevel="DontSaveSensitive" PackageSubpath="MySimpleBiml">
<Tasks>
<# for each table as asttablenode in RootNode.Tables #>
<Container Name="Transfer <#=table.Schema.Name#>_<#=table.Name#>" ConstraintMode="Linear">
<Tasks>
<ExecuteSQL Name="Truncate" ConnectionName="Target">
<DirectInput>truncate table <#=table.ScopedName#></DirectInput>
</ExecuteSQL>
<Dataflow Name="Copy <#=table.Schema.Name#>_<#=table.Name#>">
<Transformations>
<OleDbSource Name="Get Rows" ConnectionName="Source">
<DirectInput><#=table.GetTag("SQLSelect")#></DirectInput>
</OleDbSource>
<OleDbDestination Name="Set Rows" ConnectionName="Target">
<TableOutput TableName="<#=table.ScopedName#>" />
</OleDbDestination>
</Transformations>
</Dataflow>
</Tasks>
</Container>
<# next #>
</Tasks>
</Package>
</Packages>
</Biml>
Nachdem Sie dieses Paket erstellt und ausgeführt haben, sind alle Ihre Zieltabellen mit den entsprechenden AdventureWorks-Daten befüllt.
Löst dies all Ihre Probleme und Aufgaben bezüglich Datenbeladungen? Sicherlich nicht – aber Sie haben hoffentlich einen guten Eindruck erhalten, wie einfach es ist Biml mit Metadaten zu steuern und auf dieses Basis Lösungen aufzubauen.
Hat Ihnen dieser Beitrag gefallen? Dann gleich weiter zu Teil 2!
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!
[/spb_text_block]