Biml Tutorial Generating Multiple Packages At Once


In the final chapter of this tutorial, we will finish the Biml solution for generating a staging environment. In the previous chapter, we created three files:

  • One to create the environment
  • One to fetch the metadata and create the tables in memory
  • One to create a SSIS package that will create the destination tables

After running the SSIS package, we have a staging database with empty tables. Now we will generate all of the SSIS packages – one for each staging table – that will transfer the data from AdventureWorks to the staging database.

Generating the SSIS Packages

Assuming we already have the three Biml files mentioned in the introduction, all we need to do is create a final Biml file that will generate those SSIS packages. The link to download all of the files can be found at the end of this chapter.

The file contains the following code:

biml for generating staging packages

The file has tier 40. The pattern used is straight-forward:

  • Truncate the destination table.
  • In a data flow, read all data from the source table and write it to the staging table.

Again an extension method is used to simplify the code. The GetSelectSql method creates a SQL SELECT statement that fetches all the column from the table. The package has its ConstraintMode set to linear, which means the second task follows after the first task.

The expanded Biml looks like this:

expanded Biml

The file name is “x-2 Generating Staging Packages.biml”. The x in the name means the Biml script is not needed in the first step of the solution (creating the destination tables). To generate all the staging packages, select the environment file, the metadata file and the newly Biml file, right-click and choose Generate SSIS Packages.

generate all the packages

During compilation, the compiler might ask if you want to overwrite the project connection manager (which was created when the package for the destination tables was generated).

overwrite items

It will ask this for every generated package, since each package tries to create the project connection manager. Since it already exists, click on Select all at the top and click it again to remove all selections.

When the Biml has compiled all the packages – which can take some time – they are all added to the project.

generate all the packages

The control flow of a package looks like this:

control flow

The data flow:

data flow


We have come to the end of the tutorial. It’s clear Biml can help generate many packages following the same pattern in an instant. Using Biml will clear tedious manual work from your schedule, so you can focus on the harder challenges of your data project.

Additional Information

Comments For This Article

get free sql tips
agree to terms