SFSSUG: BIML Demonstration: Create ETLs for every table in a database
BIML is Business Intelligence Markup Language for SQL Server Integration Services(SSIS) .
One of the things BIML allows you to do is to design one or more packages at a high level, and have that package style be applied to a suite of multiple tables which can be selected via a query.
Another thing BIML can be used for is to copy/clone the some or all schemas and objects from one database to another.
Using the free BIML tools and Visual Studio 2015 Community Edition and SQL server Data Tools(SSDT), Lowell Izaguirre will show you his style of using BIML to create a control infrastructure, which is then used to create Packages with ETLS for some or all of your tables, with the infrastructure to use Change Tracking, Incremental loads, or truncate and reload.
The key in Lowell’s design pattern is the control table. Populating the control table, often with the necessary sql commands, alows Multiple packages may query the control table for a subset of tables , and packages are generated based on those queries. some packages for change tracking, some for incremental loads, and others for Truncate and Reload styles.
With this design pattern in place, you can generate packages to do your ETLs from one server to another in a fraction of the time it would take to develop them manually.
Lowell will emphasize the growing pains and gotchas he tripped over when developing with this powerful technology. Understanding these allows you to get a huge jump start over some of the simpler BIML examples you find on the web.
The complete example package will be available for download, ready for trivial adaptations of connection strings for you to work with.