Import von Flat Files mit Biml und XML Formatdefinitionen

This blog post has also been published in English.

Bisher haben wir mit Biml immer Daten aus anderen Datenbanken importiert. Aber was ist mit Flatfiles? Kann Biml das auch? Aber sicher!

In diesem Beispiel schauen wir uns einige sehr einfache, aus der AdventureWorks2014 Datenbank generierte, Dateien an. Wir gehen bewusst nicht auf alle Datentypen ein, aber Sie sollten im Anschluss einen guten Eindruck bekommen haben, was Biml für die Arbeit mit Flatfiles benötigt.

Um das ganze etwas flexibler zu halten, generieren wir uns nicht nur die Daten als CSV aus AdventureWorks sondern auch die zugehörigen Format-Informationen als XML. Diese erzeugen wir mit dem tool bcp.

Erzeugen Sie sich zunächst ein Verzeichnis C:\Flatfiles mit 2 Unterverzeichnissen Data und Format. Speichern Sie den folgenden Code als „generate_csv.bat” in diesem Verzeichnis ab:



bcp adventureworks2014.person.personphone out Data\PersonPhone.csv -T -t, -c
bcp adventureworks2014.person.emailaddress out Data\PersonEmailaddress.csv -T -t, -c
bcp adventureworks2014.sales.salesorderdetail out Data\salesorderdetail.csv -T -t, -c
bcp adventureworks2014.sales.salesorderheader out Data\salesorderheader.csv -T -t, -c


bcp adventureworks2014.person.personphone format nul -f Format\PersonPhone.xml -T -t, -c -x
bcp adventureworks2014.person.emailaddress format nul -f Format\PersonEmailaddress.xml -T -t, -c -x
bcp adventureworks2014.sales.salesorderdetail format nul -f Format\salesorderdetail.xml -T -t, -c -x
bcp adventureworks2014.sales.salesorderheader format nul -f Format\salesorderheader.xml -T -t, -c -x

Führen Sie nun die Datei aus. Sie sollten nun 4 CSV Dateien in Ihrem Data Verzeichnis sehen, ebenso wie die zugehörigen Format-Definitionen im Format Verzeichnis. Natürlich können Sie die verwendeten Tabellen etc. entsprechend anpassen.

Um die Daten in den SQL Server zu importieren benötigen wir hier noch eine Ziel-Datenbank. Legen Sie hierzu bitte eine Datenbank namens „MyBiml_Flatfiles“ an.

Zum Abschluss unserer Vorbereitungsarbeiten legen wir eine Biml Datei an, welche auf diese Datenbank verweist:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
	    <Connections>
        	<OleDbConnection Name="Target" ConnectionString="Data Source=localhost;initial catalog=MyBiml_Flatfiles;provider=SQLNCLI11;integrated security=SSPI"></OleDbConnection>
		</Connections>
		<Databases>
			<Database Name="MyBiml_Flatfiles" ConnectionName="Target"></Database>
        </Databases>
		<Schemas>
			<Schema Name="dbo" DatabaseName="MyBiml_Flatfiles"></Schema>
        </Schemas>
</Biml>

Wir haben nun Quelldaten und eine Zieldatenbank – es wird also Zeit, dass wir diese miteinander verbinden.

Um mit Flat Files zu arbeiten benötigt Biml ein oder mehrere Flat File Formate sowie die zugehörigen Flat File Connections. Eine Flat File Connection ist nichts anderes als die Verbindung aus einem Format sowie der eigentlichen Datei. Auch wenn Sie im wahren Leben sicherlich häufig über mehrere Dateien mit dem gleichen Format loopen, halten wir es für heute einfach und nutzen eine Datei je Formatdefinition – effektiv entspricht diese ohnehin dem Ergebnis des Exports aus AdventureWorks.

Aus Gründen der Lesbarkeit sowie der Wiederverwendbarkeit unseres Codes nutzen wir eine neue Extension Method namens GetFlatFileFormatfromXML. Sofern Sie unseren Blog Post zum Thema „Erstellen eigener Extension Methods“ noch nicht gelesen haben, macht es eventuell Sinn, diesen vorzuziehen.

Dies erlaubt uns zusätzlich, unseren Biml Code schlank und aufgeräumt zu halten:



<#@ template language="VB" tier="2" #>
<#@ Import namespace="System.io" #>
<#@ code file="../code/FlatFileExtensions.vb" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
 <FileFormats>
<# for each XmlFile as String  in Directory.Getfiles(basepath + "\Format") #>
	<#= XmlFile.GetFlatFileFormatfromXML().GetBiml() #>
<# next #>
</FileFormats>
</Biml>

Letztendlich loopen wir nur durch das Verzeichnis mit den XML Formatdefinitionen und rufen die oben genannte Extension Method für jede Datei auf.

Die Biml Datei wiederum referenziert ein Code File “FlatFileExtensions.vb”, in welchem offensichtlich die eigentliche Arbeit stattfindet:

Imports Varigence.Biml.Extensions
Imports Varigence.Languages.Biml
Imports Varigence.Languages.Biml.FileFormat
Imports Varigence.Languages.Biml.Table
Imports System.IO
Imports System.Xml
Imports System.Data 
Imports System.Runtime.CompilerServices

Module FlatFileExtension
	Public Basepath As String   = "C:\flatfiles"
	Public Locale As Varigence.Languages.Biml.Cube.Language = Varigence.Languages.Biml.Cube.Language.Lcid1033
	<Extension()> 
	Public Function ToAstTableNode(FlatFile As AstFlatFileFormatNode, Schema As AstSchemaNode) As AstTableNode
		Dim BimlTable As New AstTableNode(Nothing)
		BimlTable.Name = "FF_" + FlatFile.name
		BimlTable.Schema = schema
		For Each flatFileColumn As astflatfilecolumnnode In FlatFile.Columns
			Dim tableColumn As New AstTableColumnNode(Nothing)
			tableColumn.Name = flatFileColumn.Name
			tableColumn.DataType = flatFileColumn.DataType
        	tableColumn.Length = flatFileColumn.Length
        	tableColumn.Precision = flatFileColumn.Precision
        	tableColumn.Scale = flatFileColumn.Scale
        	tableColumn.CodePage = flatFileColumn.CodePage
			BimlTable.Columns.Add(tableColumn)
		Next
		Dim annotation As New AstAnnotationNode(Nothing)
		annotation.Tag = "ConnName"
		annotation.Text = FlatFile.Name
		BimlTable.Annotations.Add(annotation)
 		Return BimlTable
	End Function	
		
	<Extension()> 
	Public Function ToFlatfileConnection (FlatFileFormat As AstFlatFileFormatNode) As Connection.AstFlatFileConnectionNode
	Dim Connection As New Connection.AstFlatFileConnectionNode(Nothing)
	connection.Name = FlatFileFormat.Name
	connection.FileFormat = FlatFileFormat
	connection.FilePath= Basepath & "\Data\" & FlatFileFormat.name & ".csv"
	Return Connection
	End Function
	
	<Extension()> 
	Public Function GetFlatFileFormatfromXML (XmlFile As String) As AstFlatFileFormatNode
	Dim FlatFileFormat As New AstFlatFileFormatNode(Nothing)
	Dim xmldoc As New XmlDocument	
	xmldoc.Load(XmlFile)
	Dim records As XmlNodeList = xmldoc.GetElementsByTagName("RECORD").item(0).childnodes
	Dim rows As XmlNodeList = xmldoc.GetElementsByTagName("ROW").item(0).childnodes 
	Dim row As xmlnode
 	FlatFileFormat.Locale = Locale
	FlatFileFormat.Name =   path.GetFileNameWithoutExtension(XmlFile)
	FlatFileFormat.RowDelimiter =  ConvertDelimiter(records.item(records.count-1 ).attributes("TERMINATOR").value)
	FlatFileFormat.ColumnNamesInFirstDataRow = False
	FlatFileFormat.isunicode = False
	FlatFileFormat.TextQualifier="_x0022_"
	For Each record As xmlnode In records
		row = rows.item(record.attributes("ID").value-1 ) 
		Dim DataType As String = row.attributes("xsi:type").value
		Dim DatatypeID As Integer = ConvertDatatype(datatype)
		Dim Column As New AstFlatFileColumnNode(Nothing)
		column.name = row.attributes("NAME").value
		column.Delimiter = ConvertDelimiter(record.attributes("TERMINATOR").value )
		If DatatypeID= Nothing Then
			' By default, we will make this a string!
			column.DataType = DbType.String
		Else
			column.DataType = DatatypeID
		End If
		If DatatypeID= Nothing Then
			' By default, we want out strings to be 1000 Characters
			column.Length = 1000
		ElseIf DatatypeID = dbtype.AnsiString Or DatatypeID = DbType.String Then 
			column.Length = record.attributes("MAX_LENGTH").value 
		End If
		If ConvertDatatype(datatype) = dbtype.VarNumeric Then
			column.Precision = 32
			column.Scale = 16
		End If
		If DatatypeID= Nothing  Then 
			Dim columnannotation As New AstAnnotationNode(Nothing)
			columnannotation.Tag = "Original Datatype"
			columnannotation.Text = datatype
			column.Annotations.Add(columnannotation)
		End If
		FlatFileFormat.Columns.Add(column)
	Next
	Return FlatFileFormat
	End Function
	
	Public Function ConvertDatatype (CSVType As String) As String
		Select Case CSVType 
			Case  "SQLINT" 
				Return dbtype.Int32
			Case "SQLSMALLINT"
				Return dbtype.int16
			Case  "SQLVARCHAR" 
				Return dbtype.AnsiString
			Case  "SQLDATETIME" 
				Return dbtype.DateTime	
			Case "SQLMONEY"
				Return dbtype.Currency
			Case "SQLNUMERIC"
				Return dbtype.Double
			Case "SQLNVARCHAR"
				Return DbType.String
			Case "SQLUNIQUEID"
				' GUIDs should be interpreted as strings
				Return  DbType.String
			Case Else 
				Return Nothing
		End Select
	End Function
	
	Public Function ConvertDelimiter (CSVDelimiter As String) As String
		Select Case CSVDelimiter 
			Case  "\r\n" 
				Return "CRLF"	
			Case Else 
				Return CSVDelimiter
		End Select
	End Function
End Module
 
 

In dieser Datei befindet sich jedoch deutlich mehr als nur diese Extension Method:

– 2 Variablen, eine zur Definition unseres Basisverzeichnisses sowie eine zur Definition unserer Locale
– 2 zusätzliche Extension Methods “ToAstTableNode“ und „ToFlatfileConnection” – zu diesen kommen wir später
– 2 Funktionen “ConvertDatatype“ und “ConvertDelimiter“, welche uns helfen die Datentypen und Trennzeichen, welche bcp in seinen Formatdateien angibt, in das von Biml genutzte Format zu übersetzen. Diese erlauben uns auch generelle Grundeinstellungen vorzunehmen – wie zum Beispiel der Ausgabe von GUID Datentypen als String.

Unsere Extension Method erzeugt eine neue AstFlatFileFormatNode, vergibt einen Namen, Text Qualifier usw. und looped dann wiederum durch die Spaltendefinitionen und fügt diese hinzu – wegen des XML Formats müssen hierzu 2 XmlNodeLists (Records und Rows) geladen und abgeglichen werden.

Achtung: Manche der Einstellungen die wir hier statisch vergeben, passen vielleicht nicht zu Ihrem individuellen Anwendungsfall.

Die Kombination aus unserem Biml File und dieser Methode erzeugt je Datei in unserem Format-Unterverzeichnis einen „File Format“ Eintrag in Biml.

Um hiermit etwas Sinnvolles anfangen zu können, benötigen wir für jedes Dateiformat eine Tabellen-Definition sowie eine Verbindung. Diese erzeugen wir mit den beiden weiteren, oben bereits kurz erwähnten, Extension Methods. Es ist somit ausreichend in einem weiteren Biml File schlicht über die File Format-Nodes zu loopen:

<#@ template language="VB" tier="3" #>
<#@ code file="../code/FlatFileExtensions.vb" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
	<Tables>
	<# for each tbl as AstFlatFileFormatNode in rootnode.flatfileformats #>
		 <#= tbl.ToAstTableNode(rootnode.schemas(0)).GetBiml #>
	<# next #>
	</Tables>
	<Connections>
	<# for each tbl as AstFlatFileFormatNode in rootnode.flatfileformats #>
		<#= tbl.ToFlatfileConnection.GetBiml #>
	 <# next #>
 	</Connections>
 </Biml>
 

Um Daten in den SQL Server zu laden, benötigen wir hier entsprechende Zieltabellen.

Der hierfür verwendete Code sollte Ihnen mittlerweile sehr bekannt vorkommen:

<#@ template tier="4" language="VB" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Packages>
          <Package Name="01_CreateStaging" ConstraintMode="Parallel" ProtectionLevel="EncryptSensitiveWithUserKey">
            <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>

Selbiges gilt letztendlich für die Datei, mit deren Hilfe wir die Tabellen befüllen.

<#@ template tier="5" language="VB" #>
<#@ code file="../code/FlatFileExtensions.vb" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
	<Packages>
		<Package Name="02_Populate Tables" ConstraintMode="Parallel"  ProtectionLevel="DontSaveSensitive" DelayValidation="true">
			<Tasks>
			<# for each table as asttablenode in RootNode.Tables  #>
				<Container Name="Transfer <#= table.gettag("ConnName")#>" ConstraintMode="Linear">
					<Tasks>
						<ExecuteSQL Name="Truncate" ConnectionName="Target">
							<DirectInput>truncate table <#=table.ScopedName#></DirectInput>
						</ExecuteSQL>
						<Dataflow Name="Load <#=table.gettag("ConnName")#>">
							<Transformations>
								<FlatFileSource ConnectionName="<#= table.gettag("ConnName") #>" Name="<#= table.name #>" LocaleId="<#= locale #>"/>
								<OleDbDestination Name="Dest" ConnectionName="Target">
									<TableOutput TableName="<#=table.ScopedName#>" />
								</OleDbDestination>
							</Transformations>
						</Dataflow>
					</Tasks>
				</Container>
			<# next #>
			</Tasks>
		</Package> 
	</Packages>
</Biml>

Der einzige Unterschied zu dem, was Sie aus vorhergehenden Blog Posts kennen, ist die FlatFileSource. Diese benötigt einen Verweis auf eine Verbindung sowie einen Namen, zur Sicherheit definieren wir auch hier zur Vermeidung eines Sprachkonflikts nochmals die Locale.

Das war es – Sie haben nun die erforderlichen Flat Files erzeugt, die Definitionen in Biml angelegt, die Zieltabellen erzeugt und die Daten in selbige Tabellen geladen.

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!