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”.
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.
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”.
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”.
Provkör SSIS-paketet…
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
- Varigence (företaget bakom Biml/BimlScript och MIST)
- YouTube-presentation om BIML med Scott Currie (upphovsmannen till Biml/BimlScript) inspelad i Stockholm 2014
- BimlScript.com