For Each Schleifen mit Biml über Flat Files

This blog post has also been published in English.

Wenn Sie unseren Beitrag zum Thema Arbeiten mit Flatfiles gelesen haben, dann haben Sie sich vermutlich gefragt: Aber was ist, wenn ich mehrere CSV Dateien im gleichen Format habe? Ein sehr häufiger Anwendungsfall bei Flat Files. Schauen wir uns einmal an, wie das aussehen kann.

Wie immer benötigen wir etwas Vorbereitung.

Zunächst laden Sie sich bitte unsere Beispieldaten herunter und extrahieren diese nach C:\Flatfiles\ForEachLoop.

Unsere Datenbank ist wieder MyBiml_Flatfiles, hier benötigen wir eine Zieltabelle für unsere Daten:

CREATE TABLE [dbo].[FF_Inventory](
	[Warehouse] [nvarchar](50) NULL,
	[Item] [nvarchar](50) NULL,
	[Inventory] [bigint] NULL,
	[Filename] [nvarchar](35) NULL
) ON [PRIMARY] 

Dann, als letzten Schritt bevor der eigentliche Spaß beginnt, definieren wir noch unsere Biml Umgebung mit dem Flat File Format, der Flat File Connection sowie der Verbindung zu unserer Ziel-Datenbank:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
	<FileFormats>
        <FlatFileFormat Name="MyFlatFile" CodePage="1252" ColumnNamesInFirstDataRow="true" IsUnicode="false">
    		<Columns>
        		<Column Name="Warehouse" DataType="String" Length="50" Delimiter="Semicolon" />
        		<Column Name="Item" DataType="String" Length="50" Delimiter="Semicolon" />
        		<Column Name="Inventory" DataType="Int64" Delimiter="CRLF" />
    		</Columns>
		</FlatFileFormat>	
	</FileFormats>
    <Connections>
        <Connection Name="Target" ConnectionString="Data Source=.;Initial Catalog=MyBiml_Flatfiles;Provider=SQLNCLI11.1;Integrated Security=SSPI;" />
		<FlatFileConnection Name="MyFlatFile" FilePath="empty" FileFormat="MyFlatFile" />
    </Connections>
</Biml>

Jetzt sind die Vorbereitungen abgeschlossen und wir kommen zur Frage, was wir mit unserer Übung eigentlich erreichen wollen.

Wir möchten jede Datei in einem Verzeichnis in eine SQL Tabelle importieren und zusätzlich zu den Daten in der Datei selbst auch ihren Namen speichern.

Was benötigen wir dafür in SSIS?

– Eine Variable, in der wir den jeweiligen Dateinamen speichern, welche auch als Connection String für unsere Flat File Connection dient
– Eine For Each Schleife, die durch unser Verzeichnis loopt, jeden Dateinamen im Verzeichnis in unserer Variable speichert und dann jeweils einen Datenflusstask ausführt
– Eine abgeleitete Spalte, die den Variablenwert (Dateinamen) enthält, um diese in unsere SQL Tabelle importieren zu können
– Um ein „aufgeräumtes“ Ergebnis zu erzielen, einen Truncate-Task auf die Zieltabelle vor dem Befüllen

Wie sieht der Code dazu aus?



<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Packages>
        <Package ProtectionLevel="EncryptSensitiveWithUserKey" Name="For_Each_Loop" ConstraintMode="Linear">
            <Variables>
                <Variable DataType="String" Name="MyFileName"/>
            </Variables>
            <Connections>
                <Connection ConnectionName="MyFlatFile">
                    <Expressions>
                        <Expression ExternalProperty="ConnectionString">@[User::MyFileName]</Expression>
                    </Expressions>
                </Connection>
            </Connections>
            <Tasks>
                <ExecuteSQL Name="Truncate" ConnectionName="Target">
                    <DirectInput>truncate table ff_inventory</DirectInput>
                </ExecuteSQL>
                <ForEachFileLoop Name="Loop through CSV" Folder="C:\Flatfiles\ForEachLoop" FileSpecification="*.csv">
                    <VariableMappings>
                        <VariableMapping Name="0" VariableName="User.MyFileName" />
                    </VariableMappings>
                    <Tasks>
                        <Dataflow Name="Load all files in directory">
                            <Transformations>
                                <FlatFileSource Name="SRC" ConnectionName="MyFlatFile" />
                                <DerivedColumns Name="Filename">
                                    <InputPath OutputPathName="SRC.Output" />
                                    <Columns>
                                        <Column Name="Filename" DataType="String" Length="35">@[User::MyFileName]</Column>
                                    </Columns>
                                </DerivedColumns>
                                <OleDbDestination Name="Target" ConnectionName="Target">
                                    <InputPath OutputPathName="Filename.Output" />
                                    <ExternalTableOutput Table="[dbo].[FF_Inventory]" />
                                </OleDbDestination>
                            </Transformations>
                        </Dataflow>
                    </Tasks>
                </ForEachFileLoop>
            </Tasks>
        </Package>
    </Packages>
</Biml>

Wie Sie sehen, arbeiten wir die folgenden Schritte ab:

– Definition der Variable
– Zuweisung der Variable als Connection String der Flat File Connection
– Ausführung des TRUNCATE
– Definition unserer For Each Schleife mit einem Dateipfad als Quelle, einer Variablen-Zuweisung sowie dem Datenfluss-Task mit der abgeleiteten Spalte

Dieser Code sollte natürlich normalerweise so gestaltet werden, dass er mittels Parametern und CallBimlScript aufgerufen werden kann, damit das Ergebnis auch wieder verwertbar ist.

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!