Walkthrough – autogenerera inläsning av extraktfiler med Biml

By | maj 25, 2015

Denna bloggartikel handlar om hur du som arbetar med Business Intelligence och SQL Server kan spara mycket tid på att använda Biml/BimlScript. Visst finns det bättre saker att ägna sig åt än repetitiva uppgifter som att bygga mängder av i stort sett likadana SSIS-paket för inläsning av extraktfiler? Låt datorn göra jobbet istället!

Först, en väldigt kort bakgrund om Biml och BimlScript. Mer finns att läsa här.

  • Biml (Business Intelligence Markup Language) är en XML-dialekt som beskriver datamodeller, ETL-processer (d v s SSIS-paket) och multidimensionella modeller (SSAS eller BISM som det kallas numera).
  • BimlScript är en utökning av Biml där du kan skriva C# eller VB.NET kod för att göra dina Bimldokument dynamiska. Ungefär som ASP.NET i förhållande till HTML.

För att använda Biml/BimlScript har du två alternativ:

BIDS Helper

Ett gratis tillägg till Visual Studio som förutom stöd för Biml/BimlScript också ger många andra fördelar. Ett måste att ha!

Läs här hur du kommer igång och konfigurerar Visual Studio. Läs mer om BIDS Helper här.

MIST

En kommersiell produkt från Varigence (företaget bakom Biml/BimlScript). Med MIST får du mer funktionalitet och en mycket bättre miljö att jobba med Biml/BimlScript.

Det går att prova MIST gratis i 14 dagar, vilket jag starkt rekommenderar om du vill lära dig Biml/BimlScript ordentligt.

Steg 0 – Extraktfil

Jag har gjort en lista över vinnare i melodifestivalen, men innehållet spelar ingen roll för denna demo.

Year;HostCity;WinningCountry;SongTitle;Performer
2005;Kiev;Greece;My Number One;Helena Paparizou
2006;Athens;Finland;Hard Rock Hallelujah;Lordi
2007;Helsinki;Serbia;Molitva;Marija Serifovic
2008;Belgrade;Russia;Believe;Dima Bilan
2009;Moscow;Norway;Fairytale;Alexander Rybak
2010;Oslo;Germany;Satellite;Lena
2011;Düsseldorf;Azerbaijan;Running Scared;Ell & Nikki
2012;Baku;Sweden;Euphoria;Loreen
2013;Malmö;Denmark;Only Teardrops;Emmelie de Forest
2014;Copenhagen;Austria;Rise Like a Phoenix;Conchita Wurst
2015;Vienna;Sweden;Heroes;Måns Zelmerlöw

Filen är sparad i ANSI-format ifrån Notepad. Ladda ned den här om du inte själv vill skapa en fil.

Steg 1 – Skapa tabeller

Mitt exempel förutsätter att du har en databas som heter JohanBimlDemo. Du kan naturligtvis välja ditt eget namn på databasen, men då måste du också ändra i scripten.

Med MIST

Om du har MIST så kan du automatisera skapandet av tabeller. För att skapa tabellerna använder jag ett BimlScript som läser översta raden i varje extraktfil och hämtar kolumnnamnen därifrån. En tabell skapas för varje extraktfil (i vårt exempel bara en enda). Kolumnerna får datatypen ”string” (motsvarar nvarchar) och längden 4000 så att de kan rymma det mesta. Innan du kör scriptet, så ändra ”directory” så att den pekar på var du har dina filer.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<#+
	// Create tables BimlScript
	// by Johan Åhlén 2015

	// Settings here...
	const string directory = @"C:\Johan\Dev\BimlDemo";
	const char separator = ';';
#>

	<Connections>
        <Connection Name="JohanBimlDemoConnection" ConnectionString="Provider=SQLNCLI11;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=JohanBimlDemo" />
	</Connections>

	<Databases>
        <Database Name="JohanBimlDemo" ConnectionName="JohanBimlDemoConnection" />
    </Databases>

	<Schemas>
        <Schema Name="Import" DatabaseName="JohanBimlDemo" />
    </Schemas>

	<Tables>
<# foreach (var filename in System.IO.Directory.EnumerateFiles(directory, "*.txt")) { #>
		<Table Name="<#= System.IO.Path.GetFileNameWithoutExtension(filename) #>" SchemaName="JohanBimlDemo.Import">
			<Columns>
				<Column Name="RowId" DataType="Int32" IdentityIncrement="1" IsNullable="false"/>
<# var sr = new System.IO.StreamReader(filename);
   var firstRow = sr.ReadLine();
   sr.Close();
   var columnNames = firstRow.Split(separator);
   foreach (var columnName in columnNames) { #>
				<Column Name="<#= columnName #>" DataType="String" Length="4000" IsNullable="false"/>
<# } #>
	    	</Columns>
			<Keys>
				<PrimaryKey Name="PK_<#= System.IO.Path.GetFileNameWithoutExtension(filename) #>" Clustered="true">
					<Columns>
						<Column ColumnName="RowId"/>
                    </Columns>
				</PrimaryKey>
            </Keys>
		</Table>
<# } #>
	</Tables>
</Biml>

Scriptet lägger du in under Library/Utilities i MIST och väljer sedan ”Execute BimlScript”.

MIST Execute BimlScript

Därefter kan du i MIST autogenerera SQL-koden för att skapa tabellen (eller tabellerna om du har flera filer).

Utan MIST

BIDS Helper stödjer tyvärr inte (i alla fall inte på något enkelt sätt) möjligheten att autogenerera tabeller. Därför måste du skapa tabellerna manuellt.

USE [JohanBimlDemo];
GO

CREATE SCHEMA [Import] AUTHORIZATION [dbo];
GO

CREATE TABLE [Import].[EurovisionWinners]
(
	[RowId] INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED
,	[Year] NVARCHAR(4000) NOT NULL
,	[HostCity] NVARCHAR(4000) NOT NULL
,	[WinningCountry] NVARCHAR(4000) NOT NULL
,	[SongTitle] NVARCHAR(4000) NOT NULL
,	[Performer] NVARCHAR(4000) NOT NULL
);

Steg 2 – Skapa SSIS-paket

Vårt mål är att skapa ett paket per typ av extraktfil som först trunkerar mottagande tabell och därefter laddar den med data.

SSIS simple load package

Detta går att göra såväl från BIDS Helper som från MIST.

Vår scriptfil behöver generera följande typer av objekt:

  • FileFormat – som beskriver formatet på filerna vi läser.
  • FlatFileConnection – som refererar till filformatet.
  • Project – behövs för att samla ihop paketen om vi använder MIST.
  • Package – SSIS paketet.

Jag går inte igenom dessa objekt i detalj, men du kan se hur de fungerar i BimlScriptet nedan. Byt ut ”directory” mot sökvägen till dina filer innan du kör.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<#+
	// BimlScript to autogenerate tables and SSIS-packages for loading extract files
	// By Johan Åhlén 2015

	// Settings here...
	const string directory = @"C:\Johan\Dev\BimlDemo";
	const char separator = ';';
	const string codepage = "1252"; // ANSI
#>

<#+
	// This class and the list "files" will contain necessary information
	public class ExtractFileInfo
	{
		public string Path;
		public string FilenameWithoutExtension;
		public string[] Columns;
		public bool IsLastColumn(string column) { return column == Columns[Columns.Length - 1]; }
    }
	System.Collections.Generic.List<ExtractFileInfo> files = new System.Collections.Generic.List<ExtractFileInfo>();
#>
<#
	foreach (var filepath in System.IO.Directory.EnumerateFiles(directory, "*.txt")) {
 		var sr = new System.IO.StreamReader(filepath);
   		var firstRow = sr.ReadLine();
   		sr.Close();
		var efi = new ExtractFileInfo { Path = filepath, FilenameWithoutExtension = System.IO.Path.GetFileNameWithoutExtension(filepath), Columns = firstRow.Split(separator) };
		files.Add(efi);
	}
#>

	<Databases>
        <Database Name="JohanBimlDemo" ConnectionName="JohanBimlDemoConnection" />
    </Databases>
    <Schemas>
        <Schema Name="Import" DatabaseName="JohanBimlDemo" />
    </Schemas>

	<Tables>
<# foreach (var file in files) { #>
		<Table Name="<#= file.FilenameWithoutExtension #>" SchemaName="JohanBimlDemo.Import">
			<Columns>
<# foreach (var column in file.Columns) { #>
				<Column Name="<#= column #>" DataType="String" Length="4000" IsNullable="false"/>
<# } #>
	    	</Columns>
		</Table>
<# } #>
	</Tables>

    <FileFormats>
<# foreach (var file in files) { #>
		<FlatFileFormat Name="<#= file.FilenameWithoutExtension #>_Format" CodePage="<#= codepage #>" ColumnNamesInFirstDataRow="true" RowDelimiter="CRLF" IsUnicode="false">
            <Columns>
				<# foreach (var column in file.Columns) { #>
                <Column Name="<#= column #>" Length="4000" DataType="String" Delimiter="<#= !file.IsLastColumn(column) ? separator.ToString() : "CRLF" #>" MaximumWidth="4000" />
				<# } #>
            </Columns>
        </FlatFileFormat>
		<# } #>
    </FileFormats>

	<Connections>
        <Connection Name="JohanBimlDemoConnection" ConnectionString="Provider=SQLNCLI11;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=JohanBimlDemo" />
<# foreach (var file in files) { #>
		<FlatFileConnection Name="<#= file.FilenameWithoutExtension #>_FileConnection" DelayValidation="true" FileFormat="<#= file.FilenameWithoutExtension #>_Format" FilePath="<#= file.Path #>"/>
<# } #>
    </Connections>

	<Projects>
		<PackageProject Name="JohanBimlDemoProject" ProtectionLevel="EncryptSensitiveWithUserKey">
			<Packages>
<# foreach (var file in files) { #>
				<Package PackageName="Load_<#= file.FilenameWithoutExtension #>"/>
<# } #>
            </Packages>
		</PackageProject>
	</Projects>

	<Packages>
<# foreach (var file in files) { #>
		<Package Name="Load_<#= file.FilenameWithoutExtension #>" ProtectionLevel="EncryptSensitiveWithUserKey">
			<Tasks>
				<ExecuteSQL Name="Truncate" ConnectionName="JohanBimlDemoConnection">
					<DirectInput>TRUNCATE TABLE [Import].[<#= file.FilenameWithoutExtension #>];</DirectInput>
                </ExecuteSQL>
				<Dataflow Name="Load">
					<PrecedenceConstraints>
						<Inputs>
							<Input OutputPathName="Truncate.Output"/>
                        </Inputs>
                    </PrecedenceConstraints>
					<Transformations>
						<FlatFileSource Name="Src" ConnectionName="<#= file.FilenameWithoutExtension #>_FileConnection" />
						<OleDbDestination Name="Dst" DefaultCodePage="<#= codepage #>" ConnectionName="JohanBimlDemoConnection">
							<InputPath OutputPathName="Src.Output"/>
							<ExternalTableOutput Table="[Import].[<#= file.FilenameWithoutExtension #>]" />
						</OleDbDestination>
                    </Transformations>
                </Dataflow>
            </Tasks>
		</Package>
<# } #>
	</Packages>
</Biml>

Scriptet ovan innehåller även tabelldefinitionen. Om du har genomfört steg 1 så har du redan skapat tabellen, men jag tar med den här ändå för att allt skall bli komplett. BIDS Helper kommer att ignorera den.

Här har jag lagt hela scriptet så att du enkelt kan ladda ned det.

Kör scriptet

För att köra scriptet i BIDS Helper, starta Visual Studio och skapa ett nytt Integration Services Project. Högerklicka sedan på projektet och välj ”Add New Biml File”.

BIDS Helper Add New Biml File

När du har lagt till Bimlfilen och kopierat in scriptet, kör det genom att högerklicka på filen och välja ”Generate SSIS Packages”.

BIDS Helper Generate SSIS Packages

Provkör SSIS-paketet…

BIML SSIS results

Grattis – du har just fått din extraktfil inläst i databasen!

Om du istället använder MIST, så är det bara att kopiera in scriptet och välja ”Execute BimlScript” och därefter ”Build” för att få ditt SSIS-paket.

Länkar