Extra info for the next Colorado Springs SQL Server Users Group.
We are thrilled to announce Andy Leonard, will be presenting at the January 22 meeting!
“Using BIML as a Design Patterns Engine” by Andy Leonard
Business Intelligence Markup Language provides a powerful solution for creating and managing SSIS Design Patterns. Andy Leonard, one of the authors of SSIS Design Patterns, demonstrates the flexibility of BIML in this session.
Hyatt Place – Garden of the Gods
503 West Garden of the Gods Road
Colorado Springs, CO 80909
Andy was asked to tell us a little more about his session here is the information he passed along. We were excited to hear Andy was coming; now we are even more excited.
Generally speaking, it takes 1-2 hours to code an SSIS package that
moves data from a source to a destination. If you use a simple pattern for
the integration, it can take less time. But the work is mostly manual and
repetitive. It really stinks when you want to create one SSIS package per
table and you have several (or several hundred) tables.
Business Intelligence Markup Language (Biml) helps in these scenarios,
as my presentation demonstrates. We’ll start with some simple Biml used to
create empty and basic packages, but then we will leverage BimlScript –
.Net inside the Biml file – to build a single Biml file that can create
hundreds of SSIS packages.
Because the packages are dynamically created from the same pattern, they
are functionally equivalent. This translates to higher quality. And because
of the automation provided by Biml, SSIS package creation time drops
drastically. My best use-case is an incremental load project I worked on to
transfer data from approximately 900 tables into a SQL Server database.
Incremental load patterns are somewhat involved, and usually take a couple
hours per package. I like to build one package per table for separation of
concerns, testing, and support reasons. Building this by hand would have
taken 2 hours per package, or just under one person-year. Using Biml, I was
able to generate these packages in a morning.
I will be covering this in the presentation.
Hope this helps.
About Andy Leonard
Andy Leonard is CSO of Linchpin People, an SSIS Trainer and Consultant, SQL Server database and Integration Services developer, SQL Server data warehouse developer, community mentor, blogger, and engineer. He is a co-author of SSIS Design Patterns. His background includes VB and web application architecture and development; and SQL Server 2000-2012.