SSIS : Experimenting with BIML (part III)

Introduction

The third blogpost about BIML again and in this blogpost there are three examples given that can aid you building BIML scripts. I do find them useful for developing BIML Scripts.

1.  Build package with two tasks (lineair).
2.  Build package with two tasks (Parallel).
3.  Experimenting with annotation.

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

1. Build package with two tasks (lineair)

In this step We're going to create a package with two tasks (ExecuteSQLTask1 and ExecuteSQLTask2) and these tasks are lineair executed meaning that ExecuteSQL1 is executed before ExecuteSQLTask2 is executed.

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

This BIML script results in the following package:

2. Build package with two tasks (Parallel)

In the former step We've executed the task in liniear mode but in this step We'll execute them in parallel. In this step we're going to create a package with two tasks (ExecuteSQLTask1 and ExecuteSQLTask2) and these tasks are parallel executed meaning that ExecuteSQL1 is independently executed from ExecuteSQLTask2.

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

This BIML script results in the following package:

3. Experimenting with annotation

I was wondering whether I could add some annotation to the SSIS package with BIML script. As you may know there is also an option in the contextmenu of the package, add annotation. So my initial assumption was that I could add an annotation child element to the package element and a annotation is shown in the SSIS package. That would be neat but I can't get it right (at this moment). With the annotationtype you can direct the annotation to some properties of a element, for instance Description of a package, like I  do in the example shown below. That works as you see in the screendump of the Description property of a package but I want to show it on the SSIS package Pane. Let me know if you know how.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Annotations>
 <Annotation AnnotationType ="Tag">
  File: Basic.biml
  Demo biml file on how to create tables
  using BIML from the 1.6.1 BIDS Helper
  Enviroment:
  DB: 
  BIML: 1.6.1 VS2008 BIDS Helper
  (c)Hennie de Nooijer
 </Annotation>
</Annotations>
<Connections>
<Connection Name ="CnOleDBAdventureWorksLT2008" ConnectionString="Data Source=.;Initial 
        Catalog=AdventureWorksLT2008;Integrated Security=SSPI;Provider=SQLNCLI10"/>
</Connections>
<Packages>
 <Package Name="Basic" ConstraintMode="Linear">
  <Annotations>
   <Annotation AnnotationType ="Description">
    File: Basic.biml
    Demo biml file on how to create tables
    using BIML from the 1.6.1 BIDS Helper
    Enviroment:
    DB: 
    BIML: 1.6.1 VS2008 BIDS Helper
    (c)Hennie de Nooijer
   </Annotation>
  </Annotations>
  <Tasks>
   <ExecuteSQL Name="ExecuteSQLTask1" 
       ConnectionName="CnOleDBAdventureWorksLT2008" ResultSet="None">
    <DirectInput>
     SELECT * FROM SalesLT.Product
    </DirectInput>
   </ExecuteSQL>
  </Tasks>
 </Package>
</Packages>
</Biml>     

And now a description is show at the description attribute of the package:

Conclusion

Still a lot to learn about BIML Script but so far I'm impressed with the power of BIMLScript.

Greetz,

Hennie