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.

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:

• Extending Biml with C# scripts
• Using external C# scripts files within Biml

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

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.

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

The created package should look like the following:

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

Deploying objects using BimlStudio

If you are using BimlStudio, you don’t have to use C# extended scripts to generate SQL statements to execute them using SSIS Execute SQL Task. Add a Biml script and build your solution. SQL DDL statement file will be generated.

Let’s try to add the following Biml script into a BimlStudio project (from the Project View tab):

Then we have to switch this script to live mode, as shown below.

If we check the project’s logical view, we can see that BimlStudio recognizes the defined objects.

If we open the “Admin.TestTable” using the BimlStudio designer, we can see that all columns, Indexes, Keys are defined.

If we build the project, two SQL DDL statement files are generated into the output directory

The following are the SQL code generated in both files:

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:

• Agile BI – Creating Tables using Biml and BimlScript
• Bill fellows blog – Biml build Database collection nodes
• BimlScript – Biml Basics for relational databases

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. Also, we mentioned how to generate the SQL Statements using BimlStudio. 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

• Author
• Recent Posts

Hadi Fadlallah

Hadi is an SQL Server professional with more than 10 years of experience. His main expertise is in data integration. He's one of the top ETL and SQL Server Integration Services contributors at Stackoverflow.com . Also, he published several article series about Biml, SSIS features, Search engines, Hadoop, and many other technologies.

Besides working with SQL Server, he worked with different data technologies such as NoSQL databases, Hadoop, Apache Spark. He is a MongoDB, Neo4j, and ArangoDB certified professional.

On the academic level, Hadi holds two master's degrees in computer science and business computing. Currently, he is a Ph.D. candidate in data science focusing on Big Data quality assessment techniques.

Hadi really enjoys learning new things everyday and sharing his knowledge. You can reach him on his personal website.

View all posts by Hadi Fadlallah

Latest posts by Hadi Fadlallah (see all)

• An overview of SQL Server monitoring tools - December 12, 2023
• Different methods for monitoring MongoDB databases - June 14, 2023
• Learn SQL: Insert multiple rows commands - March 6, 2023