Grouping Sets Of Tables In Biml ETL Loads

Kevin Feasel

2017-09-11

Biml

Ben Weissman puts together clusters of tables for data loads:

The table meta.containers could technically also be a temporary table. We’ve decided against that so you can see what’s happening behind the scenes.

Let’s focus on the meta.tables table for now. It has three columns:

– TableName – guess what we’ll store in there
– Container – this one will hold the information, which container we want this table to be loaded it, which will be automatically populated by our stored procedure
– Cost – this column will hold the load cost of this specific table. In our opinion, this should ideally be the average time it took SSIS to load this table in the recent past. If you don’t have that information available, it might as well something like the size of this table in either Gigabytes or Rows. The more accurate this column is, the better your results will be.

The only tricky part in Ben’s code is figuring out appropriate values for Cost, but if you’ve got rough timing measures or even good priors, you can get to a reasonable solution quickly.  And if time is of the essence, you can model, simulate, and apply results as part of an analytics project.

Related Posts

Using Biml With Azure Data Factory v2

Kevin Feasel

2018-03-14

Biml, Cloud

Ben Weissman shows how you can use BimlStudio to build ADF v2 flows: As you may have seen at PASS Summit 2017 or another event, with the announcement of Azure Data Factory v2 (adf), Biml will natively support adf objects. Please note, that the native support is currently only available in BimlStudio 2018. If you’re […]

Read More

Building A Biml Project: Adding Tasks

Kevin Feasel

2018-02-28

Biml

Martin Schoombee continues his series on building a Biml project by adding SSIS tasks: The <DirectInput> tag is pretty self-explanatory and executes the stored procedure (in this case). It could also have been a SQL query. The <Results> element contains a <Result> child element for each field returned in our query. I found this a little confusing at first, but the Name property’s […]

Read More

Categories

September 2017
MTWTFSS
« Aug Oct »
 123
45678910
11121314151617
18192021222324
252627282930