don't repeat yourself - agile ssis development with biml and bimlscript (sql server days)
TRANSCRIPT
Don't Repeat Yourself -Agile SSIS development with Biml and BimlScript
Cathrine WilhelmsenSQL Server Days 2015
SQL Server Days would like to thank all of our sponsors!
Session Description
Cathrine Wilhelmsen
@cathrinew
cathrinewilhelmsen.netData Warehouse Architect
Business Intelligence Developer
Who are you? *
( * Probably not a cat)
Your work day?
(Drag, drop, drag, drop, connect, drag, drop, connect,
resize, align, drag, drop, resize, connect, align…)
job done!
new standards
...yay
Ever experienced this?
Ready for a change?
From Traditional SSIS to Agile SSIS
Traditional SSIS: Plumbing
Agile SSIS: Business Logic
Will Biml solve all your problems?
How can Biml help you?
What is Business Intelligence Markup Language?
What do you need?
...or you can invest in
How does it work?
<Biml xmlns="http://schemas.varigence.com/biml.xsd"><Packages>
<Package Name="EmptyPackage1"></Package>
<Package Name="EmptyPackage2"/>
</Packages>
</Biml>
Biml syntax
<Biml xmlns="http://schemas.varigence.com/biml.xsd"><Packages>
<Package Name="EmptyPackage1"></Package>
<Package Name="EmptyPackage2"/>
</Packages>
</Biml>
Biml syntax: Root Element
<Biml xmlns="http://schemas.varigence.com/biml.xsd"><Packages>
<Package Name="EmptyPackage1"></Package>
<Package Name="EmptyPackage2"/>
</Packages>
</Biml>
Biml syntax: Collection of Elements
<Biml xmlns="http://schemas.varigence.com/biml.xsd"><Packages>
<Package Name="EmptyPackage1"></Package>
<Package Name="EmptyPackage2"/>
</Packages>
</Biml>
Biml syntax: Elements
<Biml xmlns="http://schemas.varigence.com/biml.xsd"><Packages>
<Package Name="EmptyPackage1"></Package>
<Package Name="EmptyPackage2"/>
</Packages>
</Biml>
Biml syntax: Attributes
<Biml xmlns="http://schemas.varigence.com/biml.xsd"><Packages>
<Package Name="EmptyPackage1"></Package>
<Package Name="EmptyPackage2"/>
</Packages>
</Biml>
Biml syntax: Full vs. Shorthand Syntax
Let's generate some packages
Right-click on SSIS project and click Add New Biml File
Biml files are placed in the Miscellaneous folder
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Packages>
<Package Name="EmptyPackage1"></Package>
<Package Name="EmptyPackage2"/>
</Packages>
</Biml>
Right-click on the Biml file to Generate SSIS Packages
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Packages>
<Package Name="EmptyPackage1"></Package>
<Package Name="EmptyPackage2"/>
</Packages>
</Biml>
From Biml to SSIS
From Biml to SSIS
.biml vs .dtsx: human-readable vs ALL THE CODE!
(150% zoom) (20% zoom)
I create SSIS packages faster than that
But wait!
The magic is in the
Import
Loop
Add expressions
What is ?
BimlScript code blocks
<#@ … #>
<# … #>
<#= … #>
<#+ … #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Packages>
<# foreach (var table in RootNode.Tables) { #>
<Package Name="Load<#=table.Name#>"></Package>
<# } #>
</Packages>
</Biml>
BimlScript syntax
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Packages>
<# foreach (var table in RootNode.Tables) { #>
<Package Name="Load<#=table.Name#>"></Package>
<# } #>
</Packages>
</Biml>
BimlScript syntax: Control Blocks
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Packages>
<# foreach (var table in RootNode.Tables) { #>
<Package Name="Load<#=table.Name#>"></Package>
<# } #>
</Packages>
</Biml>
BimlScript syntax: Expression Control Block
How does it work?
Yes, but how does it work?
41
Yes, but how does it actually work?<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Packages>
<# foreach (var table in RootNode.Tables) { #>
<Package Name="Load<#=table.Name#>"></Package>
<# } #>
</Packages>
</Biml>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Packages>
<Package Name="LoadCustomer"></Package>
<Package Name="LoadProduct"></Package>
<Package Name="LoadSales"></Package>
</Packages>
</Biml>
Don't Repeat Yourself
Don't Repeat Yourself
Don't Repeat Yourself
<#@ template tier="1" #>
Don't Repeat Yourself: Split and combine Biml files
Split and combine multiple Biml files
1
2
3
<#@ include file="CommonCode.biml" #>
Don't Repeat Yourself: Include files
Don't Repeat Yourself: Include files
Don't Repeat Yourself: Include files
Don't Repeat Yourself: Include files
<#@ property name="Table" type="AstTableNode" #>
<#=CallBimlScript("CommonCode.biml", Table)#>
Don't Repeat Yourself: CallBimlScript with parameters
Don't Repeat Yourself: CallBimlScript with parameters
Don't Repeat Yourself: CallBimlScript with parameters
Don't Repeat Yourself: CallBimlScript with parameters
Don't Repeat Yourself: CallBimlScript with parameters
Don't Repeat Yourself: CallBimlScript with parameters
C# Classes and Methods
<#+
public class HelperClass {
public static string HelperMethod(string InputParameter) {
return string.Concat("InputParameter: ", InputParameter);
}
}
#>
C# Classes and Methods
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<#=HelperClass.HelperMethod("Cats")#>
</Biml>
<#+
public class HelperClass {
public static string HelperMethod(string InputParameter) {
return string.Concat("InputParameter: ", InputParameter);
}
}
#>
C# Classes and Methods: Inline
<#@ include file="HelperClass.biml" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<#=HelperClass.HelperMethod("Cats")#>
</Biml>
C# Classes and Methods: Included
<#+public class HelperClass {
public static string HelperMethod(string InputParameter) {return string.Concat("InputParameter: ", InputParameter);
}}
#>
<#@ code file="..\Code\HelperClass.cs" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<#=HelperClass.HelperMethod("Cats")#>
</Biml>
C# Classes and Methods: Mist / BimlStudio
using System.Data;
public class HelperClass {public static string HelperMethod(string InputParameter) {return string.Concat("InputParameter: ", InputParameter);
}}
Transformers(No, not those transformers, but still awesome!)
Transformers: Modify existing Biml
<#@ target type="Package" mergemode="LocalMerge" #>
<!–- <Node> = Target Type = <Package> -->
<Node>
<Variables>
<Variable Name="NewRows" DataType="Int32">0</Variable>
</Variables>
</Node>
Transformers syntax
<#@ target type="Package" mergemode="LocalMerge" #>
<!–- <Node> = Target Type = <Package> -->
<Node>
<Variables>
<Variable Name="NewRows" DataType="Int32">0</Variable>
</Variables>
</Node>
Transformers syntax: Target Type
<#@ target type="Package" mergemode="LocalMerge" #>
<!–- <Node> = Target Type = <Package> -->
<Node>
<Variables>
<Variable Name="NewRows" DataType="Int32">0</Variable>
</Variables>
</Node>
Transformers syntax: Merge Mode
Demo
GET THINGS DONE KEEP GOING
What do you do next?
Biml on Monday...
@cathrinew
cathrinewilhelmsen.net
no.linkedin.com/in/cathrinewilhelmsen
slideshare.net/cathrinewilhelmsen
Biml resources and references:
cathrinewilhelmsen.net/biml