Export in geteilte Flatfiles mit Biml

This blog post has also been published in English.

Dieser Beitrag nutzt Objekte aus dem vorhergehenden Blog Post “Export in Flatfiles mit Biml”. Bitte nutzen Sie den entsprechenden Code dieses Beitrags als Basis.

Im vorhergehenden Beitrag haben wir eine komplette Datenbank in Textdateien mit einer Datei je Tabelle exportiert. Was aber, wenn wir große Tabellen in mehrere Dateien aufteilen wollen? Ein einfacher Weg hierfür wäre die OFFSET-FETCH NEXT Funktion des SQL Servers.

Hierzu benötigen wir zunächst die effektive Anzahl an Zeilen innerhalb der Tabelle, um die Anzahl der erforderlichen Dateien zu berechnen. Wie immer gibt es verschiedene Wege dies zu erreichen. Wir erstellen einfach ein SQL Statement, dessen Ergebnis wir per CallBimlScript abrufen werden. Innerhalb des Scripts nutzen wir die COUNT(*) Funktion und ermitteln mit unserem Parameter MaxRows die Anzahl der Ausgabedateien. Beachten Sie, dass dieses Script reines T-SQL statt Biml Code zurückgibt!

<#@ template language="VB" #>
<#@ property name="tablename" type="string" #>
<#@ property name="maxrows" type="string" #>
SELECT(Rowid - 1) * <#= MaxRows #>   AS StartRow
    					, RIGHT(LTRIM(STR(100000 + Rowid)),5) AS FileID
							FROM
							(
							    SELECT CAST(COUNT(*) / <#= MaxRows #>  AS INT) + CASE
																	WHEN COUNT(*) / <#= MaxRows #>.00  = CAST(COUNT(*) / <#= MaxRows #> AS INT)
																	THEN 0
																	ELSE 1
																 END AS FileCount
							    FROM <#= tablename #>
							) AS FileCount
							INNER JOIN
							(
							    SELECT ROW_NUMBER() OVER(ORDER BY id) AS RowID
							    FROM sysobjects 
							) AS RowHelper
							ON RowHelper.RowID &lt;= Filecount

Als nächstes erstellen wir eine weitere, später per CallBimlScript aufzurufende Datei (welche wiederum selbst die erste Datei aufruft). Diese Datei akzeptiert ein Flatfile Format sowie die maximale Anzahl an Zeile je Datei als Parameter. Sie erstellt ein neues Paket, ruft das vorhergehende Script auf, um das Ergebnis in einem Dataset bereitzustellen und loopt danach durch eben dieses Set. Mittels zweier Variablen werden danach die einzelnen Dateien per dynamischem Select und Dateinamen erstellt und befüllt:

<#@ template language="VB" designerbimlpath="Biml/Packages" #>
<#@ property name="FF" type="AstFlatFileFormatNode" #>
<#@ property name="MaxRows" type="string" #>
<Package Name="Export_Split <#=ff.name #>" ConstraintMode="Linear">
    <Variables>
        <Variable DataType="Object" Name="DS" />
        <Variable DataType="String" Name="FileID">00001</Variable>
        <Variable EvaluateAsExpression="true" DataType="String" Name="Filename">"C:\\Flatfiles\\Output\\Splitted\\<#= ff #>_" +  @[User::FileID] + ".csv"</Variable>
        <Variable EvaluateAsExpression="true" DataType="String" Name="SELECT">"SELECT <#= ff.gettag("Sourcecolumns") #> FROM <#= ff.gettag("Sourcetable") #> ORDER BY <#= ff.gettag("Keycolumns") #> OFFSET " + @[User::StartRow] + " ROWS FETCH NEXT <#= MaxRows #> ROWS ONLY"</Variable>
        <Variable DataType="String" Name="StartRow">0</Variable>
    </Variables>
    <Connections>
        <Connection ConnectionName="<#= ff #>">
            <Expressions>
                <Expression ExternalProperty="ConnectionString">@[User::Filename]</Expression>
            </Expressions>
        </Connection>
    </Connections>
    <Tasks>
        <ExecuteSQL Name="Read Dataset" ConnectionName="Source" ResultSet="Full">
            <DirectInput><#= CallBimlScript("11_ExportSplitted_SELECT.biml",ff.gettag("Sourcetable"),maxrows) #></DirectInput>
            <Results>
                <Result Name="0" VariableName="User.DS" />
            </Results>
        </ExecuteSQL>
        <ForEachAdoLoop Name="For Each Loop" SourceVariableName="User.DS">
            <Tasks>
                <Dataflow Name="Export <#= ff #>">
                    <Transformations>
                        <OleDbSource Name="SRC <#= ff.name #>" ConnectionName="Source">
                            <VariableInput VariableName="User.SELECT" />
                        </OleDbSource>
                        <FlatFileDestination ConnectionName="<#= FF #>" Overwrite="true" Name="DEST <#= ff #>"/>
                    </Transformations>
                </Dataflow>
            </Tasks>
            <VariableMappings>
                <VariableMapping Name="0" VariableName="User.StartRow" />
                <VariableMapping Name="1" VariableName="User.FileID" />
            </VariableMappings>
        </ForEachAdoLoop>
    </Tasks>
</Package>

Zuletzt erstellen wir noch eine Biml Datei, welche dieses Script effektiv für eine beispielhafte Tabelle aufruft und diese in 2500 Zeilen pro Datei aufteilt:

<#@ template language="VB" tier="3" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
	<Packages>
		<#= CallBimlScript("10_ExportSplitted_Packages.biml",rootnode.fileformats("Person_Person"),2500) #>
    </Packages>
</Biml>

Mit diesen Einstellungen, sowie einer unveränderten AdventureWorks2014 Datenbank, in welcher die Tabelle Person.Person knapp 20.000 Einträge hat, sollte dies in 8 Dateien resultieren!

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!