Client Migration with Biml
ABSTRACT
Many companies scale out their databases horizontally, partitioning data by customer (or some other identifier) into separate databases or instances. Sometimes, it becomes important to move that data from one database to another. SQL Server Integration Services is a fantastic tool for ETL, but creating and maintaining dozens or hundreds of data flows by hand is exhaustingly tedious. Instead of doing it the hard way, use the BI Markup Language (Biml) to automate package development. This talk will show how we can use metadata tables and Biml to auto-create SSIS packages to migrate data from one database to another, handling foreign key constraints along the way.ADDITIONAL MEDIA
No recordings or additional media are available for this talk.SLIDES
Click here to access the slides for this presentation.
The slides are licensed under Creative Commons Attribution-ShareAlike.
DEMO CODE
Click here to access demo code for this presentation. This includes a SQL Server Data Tools project, various BIML scripts, and T-SQL scripts
The source code is licensed under the terms offered by the GPL.
LINKS & FURTHER INFO
Basic Links
- All of this started with a blog series on Biml.
- Andy Leonard is one of the best people you can know if you want to learn more about Biml.
- Catherine Wilhelmsen is a top-notch Biml blogger.
- Meagan Longoria is also an excellent person from whom to learn Biml.
- The Varigence site has tutorials, code snippets, videos, and blog posts on Biml, as well as several free and paid tools.
- Stacia Misner Vargas has a Pluralsight course entitled Building Blocks of Biml which I recommend.
- Andy Leonard, Scott Currie, and several others put together The Biml Book, an excellent way to dig deep into what Biml has to offer.
- Ben Weissman has a two-part series on traversing foreign keys based off of this talk. Part 1 walks through traversing the foreign key set, whereas Part 2 introduces parallel loads.
Tools
- SQL Server Data Tools is necessary if you want to use Biml. For Visual Studio 2017, you will need to install SSDT as part of the normal features and then install the separate components for Integration Services, Analysis Services, and Reporting Services.
- Varigence BimlStudio is the best IDE for Biml. It is fairly costly, but if you do a lot of work with Biml, the product is certainly worthwhile. One of its killer features is the ability to reverse engineer an SSIS package into Biml, saving you that initial development time.
- BimlOnline is a Software-as-a-Service offering for Biml compilation which offers the ability to reverse engineer SSIS packages into Biml. It is free during the beta period.
- Biml Express is your best free option for a Biml IDE extension to Visual Studio.
- Shannon Lowder's BimlInterrogator helps you retrieve metadata from things which typically don't have automated metadata retrieval, such as flat files or Excel files.
- BI Developer Extensions is a fully-featured plug-in for Visual Studio which enhances your SSIS experience. It includes a free version of the Varigence Biml compilation engine as well as a number of helper features that make BIDS/SSDT more useable. I would highly recommend BimlExpress over this for Biml development, but it includes some useful non-Biml features which might make it worth your while to install.