Biml Tutorial Biml and the Layered Approach


Typically, your Biml solution wonít exist out of one large Biml script, but rather out of several Biml files, where each file has a specific purpose. In this chapter weíll look at the different layers that can typically be found in a Biml project.

Biml and Tiers

In Biml, you can reference objects that were created earlier. For example, you can reference a connection and use that connection for the GetDatabaseSchema function, instead of creating a new connection. For example, in earlier chapters we had the following code:

create new connection

In this code sample a new connection called sourceConnection is created. However, chances are the very same connection is already used in your SSIS packages. So why create a new one, instead of using the existing connection manager? Suppose we already created a Biml connection object with the name Source in a separate file:

environment file

Then the code can be changed to this:

reuse connection manager

This makes your code shorter, more readable and more reusable. However, itís possible you get the following error when you try this code:

null connection

What happens? Biml doesnít find the Source connection we defined in the other Biml file. Most likely the Biml compiler expends the wrong Biml file first, so it doesnít find the Source connection in memory. We can solve this issue by specifying tiers. Tiers indicate in which order the Biml compiler should expand the Biml files. For example, the environment file where we create the connections becomes tier 10, and the other file becomes tier 20:

biml source code

Biml then processes the files from the lowest tier to the highest tier. If no tiers are specified, Biml files with no BimlScript have tier 0 and files with BimlScript have tier 1. Once you start referencing objects across different Biml files, itís recommended to explicitly specify the tiers.

Biml and Different Layers

When we create SSIS packages manually, we need to follow a specific order. First, we create the source and destination tables, if they donít exist yet. Then we create the packages and finally we can optionally create a master package that executes all other packages. In Biml we need to follow the same steps:

  1. Set-up the environment: create the connections and maybe static objects, such as databases and schemas.
  2. Read the metadata and create the table objects in memory.
  3. Create the destination tables. Biml canít generate SSIS packages if the tables used in the OLE DB Destinations donít exist.
  4. Create the packages.

Typically, we can combine step 1 and 2 in one single step, as we saw in the previous section. It will only work though if the tiers are defined. Compiling multiple Biml files at once is done by selecting the files at the same time by holding CTRL while selecting the Biml files. Then right-click a file and choose Generate SSIS Packages.

compiling multiple files at once

When we want for example to generate the SSIS packages, we also need to include the environment file (step 1), otherwise the connection managers canít be created. If the project contains many Biml files, it can become confusing which Biml files to include in which step. You can use the following naming convention to help you out.

  • 1-2 Environment
  • 1-2 Read Metadata and Create Tables
  • 1-x Create Destination Tables
  • x-2 Create Staging Packages

1-2 means the file is needed for step 1 and step 2. 1-x tells us the Biml code is only needed for step 1. x-2 means the Biml file is only needed for step 2. A file with the notation 1-x-x-4 would mean the Biml file is needed in step 1 and step 4.

In the next chapter weíll put the tiers to work and generate the destination tables in the staging database.

Additional Information

Comments For This Article

get free sql tips
agree to terms