How to generate Multiple SSIS Packages using BIML


About BIDS Helper:

A Visual Studio add-in that enhances development functionality in Business Intelligence Development Studio (BIDS) and SQL Server Data Tools (SSDTBI).

The Biml Package Generator provides the ability to create packages from Business Intelligence Markup Language (Biml). Biml is an XML-based language that allows you to describe your BI solution in a declarative fashion, similarly to using HTML to describe how a web page should appear. In addition, you can embed BimlScript (C# or VB.NET code) into Biml, in the same way that ASP.NET works with HTML. This allows for large numbers of packages to be created with a minimal amount of code.

Before we get started with Business Intelligence Markup Language, you will need to download and install the latest version of BIDSHelper from http://bidshelper.codeplex.com:

As BIML is XML-based language, it can also be coded in a notepad and then copy and paste the XML code into BIML file and then compile the code using BIML Engine which is part of BIDS Helper.

Creating Your First Biml File

Note: This article describes using BIML with SQL Server 2012 Integration Services (SSIS 2012) inside the Visual Studio 2010 (VS 2010).

Once BIDSHelper is installed, open SQL Server Data Tools (SSDT). Create a new SSIS solution and project named “BIML” as shown in Figure 1:

CreateBIMLProject

Figure 1: Creating a Project named “BIML”

Once the project has been created, open Solution Explorer right-click the project name “BIML” and click on “Add New Biml File” as shown in Figure 2.

AddNewBIML

Figure 2: Add new Biml File.

A new BIML file (BimlScript.biml) will be create as shown in Figure 3:

FirstBimlScript

Figure 3 : New Bimlscript.biml file
Double-click on BimlScript.biml file. You the biml code as shown in figure 4:

IntialBIMLCode

 

Figure 4: Initial BIML code

Let’s write four lines of XML code to generate our first SSIS Package using BIML as shown in figure 5:

FirstBimlScriptSSISPackage

Figure 5: Biml file after adding code to create a SSIS Package.

Now right-click the Bimlscript.biml and click on “Generate SSIS Packages” as shown in figure 6:

GenerateFirstSSISPackage

Figure 6 : Generating SSIS package using BIML Script.

You will see a new SSIS package MyFirstBIMLPackage.dtsx as shown in figure 7:

MyFirstBIMLPackageGenerated

 

Where BIML stands out in speeding up development :

Lets generate 5 packages by copying and pasting the above code and just altering the package name as shown in figure 8:

GenerateMultiplePackages

 

Figure 8 : Added code to create four new packages in existing BIML script.

Now right-click the Bimlscript.biml and click on “Generate SSIS Packages”, you will see five four new package as shown in figure 9:

ExecuteMultipleBIMLScript

Conculsion:

In this post we saw how to generate SSIS Packages using BIML Script. Last two steps shows how easy it is to generate multiple package which little change in code. You can write a loop instead which is another way of generating multiple packages, as of now I just wanted to show how easy it is to generate multiple SSIS Packages with little changes.

In future post I will be showing how to use small blocks of code to develop your SSIS Packages and re-use the same code in different SSIS Packages hence speeding up development time.