Metadaten basierte SSIS Pakete mit Biml – Teil 2

Metadaten basierte SSIS Pakete mit Biml – Teil 2
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 biml@solisyon.de!

Weitere Informationen zu Biml, einschließlich Terminen und Blog Beiträgen finden Sie auch auf unserer Biml Seite.

Viel Spaß beim Biml’n!

Facebook