By: Koen Verbeeck
Overview
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:
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:
Then the code can be changed to this:
This makes your code shorter, more readable and more reusable. However, it’s possible you get the following error when you try this code:
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 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:
- Set-up the environment: create the connections and maybe static objects, such as databases and schemas.
- Read the metadata and create the table objects in memory.
- Create the destination tables. Biml can’t generate SSIS packages if the tables used in the OLE DB Destinations don’t exist.
- 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.
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
- The code samples for this chapter can be downloaded here.
- An excellent overview of the use of tiers in Biml can be found in the blog post Don’t Repeat Your Biml – Tiered Biml Files.
