BIML : Generating Tasks Dynamically (part IV)

Introduction 

This is already the fourth post in row about BIML. In this particular post the generation of tasks with BIML is examined. The former posts (part I, part II and part III) were about generating the packages with the xml notation of BIML. In this blogpost C# is used for generating packages. If you are familiar with ASP or ASP.net then you may know that it's possible to generate HTML with dynamic VB or C# code. And that is exactly how you can use BIML. If you add a <#@ template language="C#" #> and <# #> or <#= #>  you integrate coding in your BIML script.

Generating Tasks Dynamically

In the example below 5 ExecuteSQL Tasks are generated from the coding. In this example I've build a loop with the for statement that starts at 1 and is looped until 5 is reached.

<#@ template language="C#" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Annotations>
  <Annotation AnnotationType ="Tag">
    File: xxxxx.biml
    using BIML from the 1.6.1 BIDS Helper
    BIML: 1.6.1 VS2008 BIDS Helper
    (c)Hennie de Nooijer
  </Annotation>
</Annotations>
<Connections>
  <Connection Name ="CnOleDBAdventureWorksLT2008" ConnectionString="Data Source=.;
  InitialCatalog=AdventureWorksLT2008; Integrated Security=SSPI;Provider=SQLNCLI10"/>
</Connections>
<Packages>
  <Package Name="Basic" ConstraintMode="Linear">
    <Tasks>
    <# int i; for (i=1; i<=5; ++i){ #>
      <ExecuteSQL Name="ExecuteSQLTask<#=i#>" ConnectionName=
              "CnOleDBAdventureWorksLT2008" ResultSet="None">
        <DirectInput>SELECT * FROM SalesLT.Product</DirectInput>
      </ExecuteSQL>
    <# } #>
    </Tasks>
  </Package>
</Packages>
</Biml>

This piece of coding results in 5 ExecuteSQL Tasks:

Conclusion

Now things are getting interesting; generation of packages from metadata. That is very useful building ETL packages.

Greetz,

Hennie

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

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

SSIS : My first BIML package (part I)

Introduction

In this blogpost I'll show how to create a simple package using BIML and the BIML Package Generator Feature in BIDS helper. I assume that you already installed BIDSHelper form Codeplex. The next thing to do is creating a new project in in BIDS and start using the BIML Package Generator Feature from BIDSHelper. I've borrowed the code from the tutorials of BIDSHelper.

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

Creating a simple BIML package

Click "Add New BIML File" and a new file is added to the miscellaneous folder in the solution called BimlScript.biml. This name is automatically generated and you can change this to another name. Right click on the filename and rename file to MyFirstBIMLPackage. 

Double clicking on the file will open the BIML editor. The first lines are already written for you. 

The first thing we have to do is creating a connection to a database. This is done in the first section of the package. The next section defines a single package that contains a dataflowtask and the dataflow task contains  two components: OLEDBSource and a Multicast.

The next step is to generate a package with this definition. Right click on the BIML file and Generate SSIS Packages from the contextmenu. A new SSIS package appears in the Packages list. 

If you view the generated package then you'll see the following package components.

Conclusion

BIML is a very intersting feature of BIDSHelper. I've tried in earlier blogsposts to create a SSIS package with the SSIS API but that is a very timeconsuming and erroneous process. I think that with the BIML support in BIDSHelper you can generate SSIS package a lot quicker.

Greetz,

Hennie

The Future Group TFG Innovation event

Thursday 1st of November The Future Group organizes the TFG Innovation event at the Amsterdam office of IBM. During this event several speakers gives a presentation about groundbreaking technologies. My specialliy interest goes to the presentation of Martijn Evers: ‘Data Vault: De moderne aanpak voor het realiseren van een centrale “Datapakkluis”’ ('Data Vault: modern approach for implementing a central "Data warevault"').  But others are interesting too.

This is a free event that is being organized by The Future Group. It starts at 18.00 hrs and has several sessions on subjects like BI, analytics, architecture and software development. So if you’re interested, don’t hesitate to register.

I hope to see you at the event!

Greetz
Hennie