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