Getting Started with Biml
Making more SSIS with fewer clicks
Who am I?
• Bill Fellows
• Database developer
• 10 years SSIS
• Lazy
• Was not bitten by Jimmy
Who are you?
• Developer
• Built some SSIS packages
• Frustrated with monotony
• Worn out your mouse
Why are you here
• Want to build packages faster
• Consistent approach
• Cost effective
Free - everybody loves free
What is Biml
• Business Intelligence Markup Language
• Domain Specific Language for describing business
intelligence objects
Why Biml
• Shorten development time
• Metadata driven solutions
• Alternatives: .NET, EzAPI,
BIxPress
Pre-requisites
• Basic SSIS knowledge
• Visual Studio/BIDS/SSDT
2005/2008/2010/2012/2014
• BIDS Helper
• or
• Mist
Hello World
<Biml
xmlns="[Link]
<Packages>
<Package
Name="SimpleBimlPackage"
ConstraintMode="Linear"
/>
</Packages>
</Biml>
Demo Hello world
schema
Mist table
fact
biml hadron
schema
Bids Helper ssis
cubes
Basic Data Flow
<Package
Name="BasicDataFlow"
ConstraintMode="Linear">
<Variables>
<Variable
Name="RowCountNew"
DataType="Int32"
Namespace="User">0</Variable>
</Variables>
<Tasks>
<Dataflow
Name="DFT
Basic
Dataflow">
<Transformations>
<OleDbSource
Name="OLE_SRC
Demo"
ConnectionName="AdventureWorks"
>
<DirectInput>
SELECT
[Link],
[Link]
FROM
[Link]
AS
SC;
</DirectInput>
</OleDbSource>
<RowCount
Name="CNT
New"
VariableName="[Link]"/>
<OleDbDestination
Name="OLE_DST
RAW_SalesCurrency"
ConnectionName="TypeMoreClickLessLocal"
KeepIdentity="false"
TableLock="true"
UseFastLoadIfAvailable="true"
KeepNulls="true">
<ExternalTableOutput
Table="[RAW].[SalesCurrency]"/>
<Columns>
<Column
SourceColumn="CurrencyCode"
TargetColumn="CurrencyCode"></Column>
<Column
SourceColumn="Name"
TargetColumn="CurrencyName"></Column>
</Columns>
</OleDbDestination>
</Transformations>
</Dataflow>
</Tasks>
</Package>
Demo Data Flow
Real World Biml
Code, lots of code
• Lookups
• Conditional splits
BimlScript
• Biml + .NET Scripting
• Classic ASP for Biml
• Painful in Visual Studio
Beware Copy & Paste
(before)
<Biml
xmlns="[Link]
<Packages>
<#
for(int
i
=
1;
i
<=
5;
i++)
{
#>
<Package
Name="MyPackage<#=i#>"
ConstraintMode="Linear">
<Tasks>
<Dataflow
Name="My
Dataflow">
</Dataflow>
</Tasks>
</Package>
<#
}
#>
</Packages>
</Biml>
Beware Copy & Paste (after)
<Biml
xmlns="[Link]
<Packages>
<#
for(int
i
=
1;
i
<=
5;
i++)
{
#>
<Package
Name="MyPackage"
<#=i#>"
ConstraintMode="Linear">
<Tasks>
<Dataflow
Name="My
Dataflow">
</Dataflow>
</Tasks>
</Package>
<#
}
#>
</Packages>
</Biml>
Includes
• <#@ include file="inc_Connections.biml" #>
Useful bimlscript
• GetTableSql
• [Link]
• CallBimlScript
Quick Reference: Biml Extensions [Link]
Mist only
• Transformers
Resources
• BimlScript Resources [Link]
Resources
• Biml Forum [Link]
forumName=Biml
• StackExchange [Link]
tagged/biml?sort=newest&pageSize=50
More biml resources
• Stairway to Biml [Link]
100552/
• [Link]
100585/
• Stairway to Biml [Link]
100550/
• What is Biml? - Level 1 [Link]
articles/BIML/100552/
• Biml Basics - Level 2 [Link]
Stairway+Series/100585/
Scott Currie [Link] @scottcurrie
Paul S Waters @pswaters
Andy Leonard [Link] @andyleonard
Tim Mitchell [Link] @tim_mitchell
David Stein [Link] @made2mentor
Marco Schreuder [Link] @in2bi
Cathrine Wilhelmsen [Link] @cathrinew
Reeves Smith @SQLReeves
Peter Avenant [Link] @BimlDownunder
More at SQL Saturday 387?
• “Somebody Got BIDS Helper in my Data Tools!” 2:30
PM Room DE 335
• “Building an Enterprise Data Repository with BIML”
2:30 Room 326
Thank you
Bill Fellows @billinkc
[Link]@[Link] [Link]