Skip to content

2. Getting started

Jump to bottom
Bas van den Berg edited this page Mar 23, 2021 · 52 revisions

Assuming that you just installed BETL I'll give you an example on how to use it. ( complete code can be found at the end). In this guide we will use the Contoso sample database, but you can easily replace this with your own database.

Prerequisits

  1. Please download and install the Contoso sample database ( https://www.microsoft.com/en-us/download/details.aspx?id=18279 ). You can skip this step if you have another sample database.

Steps

1. Ingest object tree meta data

First we need to extract the meta data that tells us which tables there are and under which database, schema and server. This is called the object tree. Ingesting is synonym for importing.

Let's have a look at the current meta data repostory. The following command shows you the BETL meta data repository.

exec betl.dbo.info

It will return:

  • the objects ( of different object types. e.g. tables and users). On a fresh install it will give you only 2 objects. The first is the LOCALHOST server object and the second object is of type user. This object stores user bound properties ( e.g. log_level).
  • column meta data relating to objects ( column name, data type, etc).
  • properties that can be bound to objects ( e.g. template_name_create_table which specifies the default template for creating tables. This property can be linked to any level in the object tree. e.g. a specific table, schema or database).

You can refresh the meta data using betl.dbo.refresh. The second argument specifies the nesting level in the object tree (default=0).

-- update meta db with databases and schemas in LOCALHOST 
exec betl.dbo.refresh 'LOCALHOST', 1

-- see what happened
exec betl.dbo.info

-- update meta db with tables in [AdventureWorks2017]
exec betl.dbo.refresh '[AdventureWorks2017]' , 1 --  @object_tree_depth
exec betl.dbo.info
  • Note that refreshing is not mandatory, because it will be invoked by push later on as well. (As long as the database exists in the object tree. So refreshing localhost is mandatory when you added a new database. You can also refresh the specific database you just added).

Push a table using a simple truncate insert template

Suppose we want to copy AdventureWorks2017.Production.Product to [My_Staging].[AW].[Product] using a simple Truncate insert template ( template_id =1 )

aw_sample_1

  1. Make sure that you have the AdventureWorks2017 database. ( download it here )

  2. Create an empty Staging database called My_Staging and a schema called AW. (We put every source system in its own schema).

-- make sure that you have a target staging database with a AW schema
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'My_Staging')
   create database My_Staging
GO
use My_Staging
GO
IF NOT EXISTS ( SELECT schema_id FROM My_Staging.sys.schemas WHERE name = 'AW')
  exec('create schema [AW] AUTHORIZATION [dbo]')

After this db and schema are created they must be added to the meta data

-- update meta db with databases and schemas in LOCALHOST 
exec betl.dbo.refresh 'LOCALHOST', 1

-- update meta db with schemas in [My_Staging]
exec betl.dbo.refresh '[My_Staging]'

Before we can invoke the push we must tell BETL 2 things:

  • template_id. Which TSQL template will be used by default for pushing tables.
  • target_schema. To what location do we want to push our data.
  1. Set template_id
-- Set the propery template_id for the entire AdventureWorks2017 database. Each table will be copied using a simple truncate / insert when invoked by push. 
exec betl.dbo.setp 'template_id', 1, '[AdventureWorks2017]'
  1. Set target_schema_id ( the object_id for [My_Staging].[AW] )
-- For all schemas in the AdventureWorks2017 database we set the target schema to [My_Staging].[AW]
exec betl.dbo.set_target_schema '[AdventureWorks2017]', '[My_Staging].[AW]'
  • note that set_target_schema is just a simple wrapper that looks up the schema id and then calls betl.dbo.setp. ** note that step 3 and 4 only needs to be done once for setting up betl for this database.
  1. push

Now we are ready to generate the truncate insert tsql.

exec betl.dbo.push 'AdventureWorks2017.Production.Product'

This will start the push. if all goes well you will see Query executed succesfully. Have a look at the messages to see what has happened.

-- optionally invoke this to increase the logging:
-- exec betl.dbo.setp 'log_level', debug
-- or this to print the sql instead of executing it
-- exec betl.dbo.setp 'exec_sql', 0

One step further? Use this to transfer all tables in Adventureworks using the simple truncate insert template into your staging db.

exec betl.dbo.push '[AdventureWorks2017].[Production].%'
-- If you want to see everything that is going on under the hood. Issue next statement. By default the log level is set to INFO. 
-- exec betl.dbo.setp 'log_level', 'verbose'
  • Note that brackets are mandatory when using wildcards.

Reporting

batch report 1

batch report 2

The complete code

exec betl.dbo.info

-- update meta db with databases and schemas in LOCALHOST 
exec betl.dbo.refresh 'LOCALHOST', 1

-- see what happened
exec betl.dbo.info

-- update meta db with tables in [AdventureWorks2017]
exec betl.dbo.refresh '[AdventureWorks2017]' , 1 --  @object_tree_depth
exec betl.dbo.info

-- make sure that you have a target staging database with a AW schema
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'My_Staging')
   create database My_Staging
GO
use My_Staging
GO
IF NOT EXISTS ( SELECT schema_id FROM My_Staging.sys.schemas WHERE name = 'AW')
  exec('create schema [AW] AUTHORIZATION [dbo]') 

-- Set the propery template_id for the entire AdventureWorks2017 database. Each table will be copied using a simple truncate / insert when invoked by push. 
exec betl.dbo.setp 'template_id', 1, '[AdventureWorks2017]'

-- For all schemas in the AdventureWorks2017 database we set the target schema to [My_Staging].[AW]
exec betl.dbo.set_target_schema '[AdventureWorks2017]', '[My_Staging].[AW]'

exec betl.dbo.push 'AdventureWorks2017.Production.Product'

-- optionally invoke this to increase the logging:
-- exec betl.dbo.setp 'log_level', debug
-- or this to print the sql instead of executing it
-- exec betl.dbo.setp 'exec_sql', 0

exec betl.dbo.push '[AdventureWorks2017].[Production].%'
-- If you want to see everything that is going on under the hood. Issue next statement. By default the log level is set to INFO. 
-- exec betl.dbo.setp 'log_level', 'verbose'
Clone this wiki locally