Biml Tutorial Creating Relational Objects with Biml


It hasnít really been explained in much detail before in this tutorial, but next to Integration Services packages Biml can also generate other objects. If you use BimlStudio, you can create Analysis Services models, but you can also create relational objects Ė such as tables and columns Ė in memory. Having those objects in memory helps you with the creation of your packages later on.

Creating Databases and Schemas

Any object from a relational database can be modelled in Biml. Letís start with our destination: the staging database. We can define the database and the schema with the following code:

create DB and schema in Biml

The database is linked to the connection we defined first. The dbo schema is also linked to the database. Since thereís only one database and schema, we typed the code by hand. The tables will be created from metadata.

Creating the Tables

Now we will use the GetDataseSchema function we learned all about in the previous chapter. The following code snippet fetches all the metadata we need from the AdventureWorks database.

fetch metadata

Now we will loop over all the tables and create them in Biml, as well as their respective columns. The latter part is done with GetBiml, an extension method that generates the Biml code needed to create an object.

create tables and columns

The tables are linked to the schema and database defined earlier. When the script is saved, the expanded Biml code can be inspected in the preview.

created tables in memory

As you can see, the GetBiml method created the necessary Biml code to add the columns to the tables. As you might notice, some columns donít have a data type defined. This is typical Biml behavior: if an attribute isnít specified, the default value is taken. For columns, the default data type is Int32.

All of these tables and columns now exist in memory, which means you can reference them at other places in your code. You can for example fetch the SELECT statement for a specific table or the CREATE TABLE statement. In one of the next chapters, weíll create an SSIS package that will create all of the destination tables in the target database. But first, weíll discuss the layered approach of a Biml solution.

Comments For This Article

get free sql tips
agree to terms