By: Koen Verbeeck
Overview
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:
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.
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.
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.
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.
