SSIS : Experimenting with BIML (Part II)

Introduction

This is a blogpost about BIML, written in a serie of more blogposts. In these blogpost I'll discover the possibillities of BIML and how to generate SSIS packages. I've already posted a blogpost about BIML and this is the second blogpost about BIML. In this blogpost three examples of BIML script and the result of the generation of SSIS packages is shown:

1. Build a simple package with BIMLscript
2. Build a multiple packages with BIMLscript
3. Build package with a database connection and a task

The BIML script that is used for this blogpost is from the BIDSHelper 1.6.1.

1. Build a simple package with BIMLscript

In this first step we will start with building a simple package with BIML Script. Initially the BIML script starts with two lines of code: <Biml> and </Biml>. In order to create a package you need to add the lines <Packages> <Package></Package></Packages> to the BIML Script. There is an extra attribute needed at the Package element. The ConstraintMode property is part of packages and containers. This property controls how precedence constraints are generated in the control flow. If you want all tasks to be run in sequence, you can set the ConstraintMode to Linear. It seems that this element is mandatory.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
 <Packages>
  <Package Name="Basic" ConstraintMode="Linear">
  </Package>
 </Packages>
</Biml>

2. Build multiple packages with BIMLscript

In this step we are going to build multiple packages with a single BIML script. As you may already know the <Packages> element suggest that you can create multiple packages with a BIMLScript. Let's try this with the following code:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Packages>
 <Package Name="Basic1"  ConstraintMode="Linear"></Package>
 <Package Name="Basic2"  ConstraintMode="Parallel"></Package>
 <Package Name="Basic3"  ConstraintMode="Linear"></Package>
</Packages>
</Biml>

And, yes this piece of code generates three packages in the BIDS project. There are now three packages named Basic1, Basic2 and Basic3.

3. Build package with a database connection and a task

In this step we are going to create a simple package with a connection. I've tried multiple coding but I can't get it working without adding a task. Let me know if you do find a shorter solution. In this case I've added a <connections> element, a <Tasks> element, an ExecuteSQL element and a <DirectInput> element.  

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Connections>
 <Connection Name ="CnOleDBAdventureWorks2008" ConnectionString="Data Source=.                ;Initial Catalog=AdventureWorksLT2008;Integrated Security=SSPI;Provider=SQLNCLI10"/>
</Connections>
<Packages>
 <Package Name="Basic" ConstraintMode="Linear">
  <Tasks>
   <ExecuteSQL Name="ExecuteSQLTask1" ConnectionName="CnOleDBAdventureWorks2008" 
              ResultSet="None">
    <DirectInput>
  SELECT * FROM SalesLT.Product
    </DirectInput>
   </ExecuteSQL>
  </Tasks>
 </Package>
</Packages>
</Biml>

This results is shown in the following screenshot:

Conclusion

This is really a powerful scripting language that can really help to automate generating packages. What a difference with the API of SSIS!!

Greetz,

Hennie