IN2BI, Microsoft Business Intelligence
data, stories & insights
rss | email | twitter

Making your Biml files less complex

The combination of XML and C# code in a Biml file can make Biml files very complex. In web development Microsoft overcame this problem by introducing the code-behind model with one file for the static (html) text and a separate file for the dynamic code. Unfortunately this is not possible with Biml. There are however some ways to make your files less complex:image

  1. Separate files
  2. Move code logic to T-SQL

In this post I’ll briefly describe the first option and use some examples to explain the second option.


Separate files

You can use the include directive to insert a piece of static xml of another file in your Biml file. John Minkjan has a nice example on his website.
If you need more dynamic content you can also opt for the CallBimlScript function which will allow you to use properties. Of course you will have to handle these properties in the callee file.


Move some code logic to T-SQL

A typical Biml solution exists not only of Biml files but also of metadata that describe the packages that you want to build. I typically use a table mapping table and a column mapping table in a SQL Server database. This allows me to create a stored procedure that combines information from both tables in one dataset with all relevant information in one row.

For the next examples I will use the following information in this source to target mapping table:

image


Using FOR XML PATH(‘’) in T-SQL

With the “FOR XML PATH(‘’)” syntax you can transform columns into a string. The T-SQL statement:

SELECT srcColumnList = SELECT  ', ' + srcColumn 
FROM meta.ColumnMapping WHERE srcColumn is not null
FOR XML PATH ('')

returns: , Id, f_name, l_name, sex a string you can almost use as a column list in a source component in the SELECT <column list> FROM <source table> statement. Almost … because you’ll have to remove the first comma and handle strange characters in column names. So you’ll have to do some stuff to remove this comma:

SELECT srcColumnList = STUFF((SELECT  ', ' + QUOTENAME(srcColumn) FROM meta.ColumnMapping WHERE srcColumn is not null
FOR XML PATH('')),1,2,'')

which returns the string we need: [Id], [f_name], [l_name], [sex]


Creating the data conversion task

Expanding on the previous statement you can create additional dynamic content for your Biml solution. In this case the column definition in  a Data Conversion Task. In Biml you would write:

<DataConversion Name="DC">
  <Columns>
   
<Column SourceColumn="f_name" TargetColumn="FirstName"
            DataType="AnsiString" Length="40" CodePage="1252" />
     <…more columns …>

</Columns>
</DataConversion>

To create the column list for this data conversion use the statement:

SELECT DCColumns = REPLACE(REPLACE(STUFF(
    (SELECT  char(10) + '<Column SourceColumn="' + srcColumn 
        + '" TargetColumn="' + tgtColumn
        + '" DataType="' + tgtDataType
        + CASE WHEN tgtDataType='AnsiString'
        THEN '" Length="' + CAST(tgtLength AS varchar(10))
             + '" CodePage="1252" />'
        ELSE '" />' END
     FROM meta.ColumnMapping
     FOR XML PATH('')),1,1,''),'&lt;','<'),'&gt;','>')

Which returns the Biml string we need:

<Column SourceColumn="Id" TargetColumn="CustomerID"
     DataType="Int32" />
<Column SourceColumn="f_name" TargetColumn="FirstName"
     DataType="AnsiString" Length="40" CodePage="1252" />
<Column SourceColumn="l_name" TargetColumn="LastName"
     DataType="AnsiString" Length="40" CodePage="1252" />
<Column SourceColumn="sex" TargetColumn="Gender"
    DataType="AnsiString" Length="6" CodePage="1252" />

Some remarks to the SQL statement:

  • We don’t really need the char(10), but the line break is convenient when we look at the results.
  • Since this query uses the for xml syntax and xml can’t handle the opening en closing tag signs. SQL server replaces them with their escape codes. We have to use the REPLACE function to change these escape codes back into the opening and closing tag signs.

 

Creating the stored procedure

For this example I would create the following stored procedures that combines the two metadata tables:

CREATE PROCEDURE meta.getPackageDetails (@MappingType varchar(50)) AS
SELECT
      PackageName = t.MappingName
    , TargetTable = t.tgtSchema + '.' + t.tgtTable
    , TargetConnection = t.tgtConnection
    , SourceTable = t.srcSchema + '.' + t.srcTable
    , SourceConnection = t.srcConnection
    , srcColumnList = STUFF((SELECT  ', ' + QUOTENAME(srcColumn)
        FROM meta.ColumnMapping
        WHERE srcColumn is not null
        AND TableMappingID=t.TableMappingID
        FOR XML PATH('')),1,2,'')
    , DCColumns = REPLACE(REPLACE(STUFF(
        (SELECT  char(10) + '<Column SourceColumn="' + srcColumn
        + '" TargetColumn="' + tgtColumn
        + '" DataType="' + tgtDataType
        + CASE WHEN tgtDataType='AnsiString'
        THEN '" Length="' + CAST(tgtLength AS varchar(10))
            + '" CodePage="1252" />'
        ELSE '" />' END
        FROM meta.ColumnMapping
        WHERE TableMappingID=t.TableMappingID
        FOR XML PATH('')),1,1,''),'&lt;','<'),'&gt;','>')
FROM meta.TableMapping t
WHERE t.MappingType = @MappingType

Less complex Biml file

The Biml file that you need to create the packages:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <#@ include file="Connection.biml" #>

    <Packages>
    <# string sConn =  “Provider=SQLNCLI10;Server=.\\SQL2012;
       Initial Catalog=BimlSamples;Integrated Security=SSPI;"; #>
    <# string sSQL = string.Format("Exec meta.getPackageDetails
      {0}", "dim"); #>
    <# DataTable tblPackages = ExternalDataAccess.GetDataTable
      (sConn,sSQL); #>
    <# foreach (DataRow pkg in tblPackages.Rows){ #>
       
        <Package Name="<#=pkg["PackageName"]#>"
                ConstraintMode="Linear">
            <Tasks>

        <Dataflow Name="DFT <#=pkg["PackageName"]#>">
          <Transformations>
            <OleDbSource Name="ODS Source"
              ConnectionName="<#=pkg["SourceConnection"]#>" >
              <DirectInput>
                SELECT <#=pkg["srcColumnList"]#>
                FROM <#=pkg["SourceTable"]#>
              </DirectInput>
            </OleDbSource>
           
            <DataConversion Name="DC">
              <Columns>
                <#=pkg["DCColumns"]#>
              </Columns>
            </DataConversion>

            <OleDbDestination Name="ODD Target"
                ConnectionName="<#=pkg["TargetConnection"]#>">
              <ExternalTableOutput
                 Table="<#=pkg["TargetTable"]#>"/>
            </OleDbDestination>

          </Transformations>
        </Dataflow>
      </Tasks>
        </Package>
        <#}#>
    </Packages>
</Biml>
<#@ import namespace="System.Data" #>

Conclusion

In this blog post I discussed some ways to make you Biml files less complex. You can move (semi)-static content to other files and import them with the include directive or use the CallBimlScript function. And you can move parts of complicated Biml code to T-SQL. The choice of how much you will move to T-SQL will largely depend on your proficiency in C# or T-SQL.

» Similar Posts

  1. Creating a Meta Data Driven SSIS Solution with Biml - 4. Creating the Table Packages
  2. Creating a Meta Data Driven SSIS Solution with Biml - 5. Creating the Master Package
  3. Creating a Meta Data Driven SSIS Solution with Biml - 3. Creating a Table Package

» Trackbacks & Pingbacks

  1. Correct metadata is essential for every automation solution. When I started working with BIML I used one table that describes the source to target table mapping. (see the “ Creating a Meta Data Driven SSIS Solution with BIML ” series) And that works fine

» Comments

    There are no comments.

Comments are closed