Building databases using BIML

In the previously published articles in this series, we talked about the Business
Intelligence Markup Language (BIML) and how to use the technology
to build and manage SQL Server Integration Services (SSIS) packages. Besides managing SSIS packages, we can also
manage SSAS models and build databases using BIML.

In this article, we will explain how to build databases using BIML. We will be covering handling databases objects
such as schemas, tables, columns, indexes, and other objects.

To build databases using BIML, developers have to define database objects using this language, then use BimlScript
(VB or C#) to generate the CREATE SQL statement for each object to be used within an SSIS Execute SQL Task.

Getting things ready

Before starting our tutorial, we should make sure that we already have all prerequisites installed on our machine.
Our previously published article Getting started with BIML should be a great reference.

The first step in this tutorial is to create an Integration Services project using Visual Studio. Then we should add
a new Biml Script file to our solution.

Adding a new Biml script file

After adding the Biml script, we should define a connection to the SQL Server instance. To do that, we will use an
OLE DB connection. We should add the following code within the Biml script:

Databases

Databases must be defined within the “databases” element. Each database must be determined at least using a
“database” tag where the name and connection attributes must be defined, As an example:

Note that these elements must be defined if you are looking to build databases, or even you are connecting to an
existing database. Besides, you can also configure file groups, files, partition schemas, and functions, as
mentioned in the official documentation.

Schemas

The second object we may need to define while building databases is the schema. Using Biml, you can create schemas
or even configure an existing one. Schemas must be defined within the “Schemas” element where each schema is
determined within the “Schema” tag; the name and the related database name must be defined as attributes:

Tables

The third object type supported in BIML and needed while building databases is the table. Similar to other object
types, tables must be defined within the “Tables” element. Each table is defined using a “Table” tag where the table
name and the schema name are defined as attributes. Note that the schema name must be fully qualified (<database
name>.<schema name>):

If you need to create tables on the default schema, you can use the following code:

Columns

The next step after adding a table element is to define columns. Columns are defined within the element “Columns”
under the “Tables” element. Each column is defined using a “column” tag.

For each column, several properties (attributes) can be defined, such as the name, data type, is nullable, identity,
length, precision, scale, computed column expression, and others.

Identity column

In order to define an identity column, the column data type must be numeric, and we should specify the identity seed
and incremental values as follows:

A column with a default value

Default values are added to new records when no value is specified for a specific column. We can use the “Default”
attribute to set a default value as follows:

Non-nullable column

To prevent inserting null values into a column, you can add a “not null” constraint by using the “IsNullable”
attribute as follows:

String columns

There are two types of strings that can be defined using Biml:

  1. Short string: Strings that have a maximum length property (Example: nvarchar(255))
  2. Long string: String where the maximum length is not specified (Example: nvcarchar(max))

To add a long string column, we should set the length attribute to -1 as follows:

If length is set to another number, then it is considered as a short string. As an example:

Decimal columns

Columns having decimal data type specified have two additional properties (attributes): scale and precision, where
the precision is the number of digits in a number, and scale is the number of digits to the right of the decimal
point in a number. As an example:

Computed Columns

A computed column is a column that is not physically stored in the table. It can be used to calculate a value based
on another existing column. As an example:

Keys

While foreign keys are defined under the “columns” element, other keys must be defined under the “Keys” element
within the “Table” element.

Single column Foreign Keys

Foreign keys are added under the “Columns” element using the “TableRefernce” tag. We should specify the fully
qualified reference table name and the primary column name as follows:

Note that you can specify if the foreign key constraint should be created or checked using the “ForeignKeyConstraintMode” attribute.

Multiple column foreign keys

If the primary-foreign relation is created over multiple columns, we should use the “MultipleColumnTableReference” element to define the relation. Each column must have a separate element, but all columns used within a relation must have the same group name as follows:

Primary key

The primary key is defined within the keys element. All columns used within the primary key must be defined as follows:

Unique key

To create a unique key constraint, we must use the “UniqueKey” element similar to adding primary keys:

Indexes

The last object we will be covering is the index. Indexes are defined within the “Indexes” element under the “Table”
element. The index columns must be specified as follows:

If we are looking to create a clustered index, we should add the “Clustered” attribute in the “Index” element as
follows:

Other available index configurations can be found in the official documentation.

Deploying objects

After explaining how to define the objects that we may need to build databases, we should deploy them. BimlScript
gives the ability to generate the DDL statement to create these objects.

If you are not familiar with BimlScript, you can refer to our previously published articles in this series:

As an example, you can use the following code within the Biml root node below the database objects definitions:

Extending Biml with C# script to build databases

This will create a package with a single Execute SQL Task where the command is an SQL statement that creates the
databases, schemas, and tables.

Before generating the SSIS package, make sure to check if your Biml code contains errors using the “Check Biml for
errors” option.

Check Biml for errors

If no error is found, you should click on “generate SSIS packages” to build your package.

generate SSIS package

The created package should look like the following:

Generated SSIS package where the Execute SQL task is used for build database defined in the Biml metadata.

You can check a working example at the BI developer extensions website.

External Links

There are more object types that can be created using BIML, which are not mentioned in this article. You can refer
to one of those links for more details:

Conclusion

In this article, we talked about building databases using Biml. We covered many object types such as database,
schemas, tables, indexes. Since Biml can only be used within business intelligence projects, we used an SSIS package
to execute the generated DDL statement using an Execute SQL Task. In the end, we provided some external links that
contain more detailed information about this process and more object type definitions.

In the next articles in this series, we will be talking about building and managing SQL Server Analysis Services
(SSAS) tabular and multidimensional models.

Table of contents

Hadi Fadlallah
Latest posts by Hadi Fadlallah (see all)

Author: admin

Leave a Reply

Your email address will not be published.