BIML Series, Part 1, BIML Development approach

This summer I will post quite a bit about BIML. In this post I will share how one can develop BIML packages without purchasing additional software. I installed Microsoft Visual C# 2008 express edition, because it hardly occupies any disk space and is a free download. Within Visual C# is run this code:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.ComponentModel;
using System.Text;
using System.Diagnostics;
using System.Windows.Forms;


 public class ExtractTable
    {
        public string PackageName { get; set; }
        public string SequenceName { get; set; }
        public string DataFlowName { get; set; }
        public string SchemaName { get; set; }
        public string SourceSystemName { get; set; }
        public string SelectQuery { get; set; }
        public string SQLFlavour { get; set; }
        public string ExtractTableName { get; set; }
        public string SourceTableName { get; set; }
        public string ArchiveProcedure { get; set; }


        private static string metadataConnectionString = "Server=localhost;Database=meta;User Id=lalal; Password=dada;";


    private static DataTable getData(string query)
    {
        DataTable dataTable = new DataTable();

        using (SqlDataAdapter dataAdapter = new SqlDataAdapter(query, metadataConnectionString))
        {
            dataAdapter.Fill(dataTable);
        }

        return dataTable;
    }

    private static DataTable getData(string query, string parameter, string parameterValue)
    {
        DataTable dataTable = new DataTable();

        using (SqlConnection conn = new SqlConnection(metadataConnectionString))
        {
            using (SqlCommand cmd = new SqlCommand(query, conn))
            {
                cmd.Parameters.AddWithValue(parameter, parameterValue);
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);

                adapter.Fill(dataTable);
            }
        }

        return dataTable;
    }

 private static ExtractTable fillExtractTable
    (
        string PackageName,
  string SequenceName,
        string DataFlowName,
        string SchemaName,
        string SourceSystemName,
        string SelectQuery,
        string SQLFlavour,
        string ExtractTableName,
  string SourceTableName,
  string ArchiveProcedure
    )
    {
        ExtractTable extractTable = new ExtractTable();

        extractTable.PackageName = PackageName;
  extractTable.SequenceName = SequenceName;
        extractTable.DataFlowName = DataFlowName;
        extractTable.SchemaName = SchemaName;
        extractTable.SourceSystemName = SourceSystemName;
        extractTable.SelectQuery = SelectQuery;
        extractTable.SQLFlavour = SQLFlavour;
        extractTable.ExtractTableName = ExtractTableName;
  extractTable.SourceTableName = SourceTableName;
  extractTable.ArchiveProcedure = ArchiveProcedure;
  
        return extractTable;
    }
    [STAThread]
    static void Main()
    {
      //  Package objects
 var extractTables = new List();

 //  Variables used to retrieve and write data
 string query;
 DataTable tblExtractTables;

 // Add ExtractTables
    query = "SELECT top 1 "
    + "   'Extract_Entrino_' + bet.SourceTableName AS PackageName,"
    + "   bet.SequenceName,"
    + "   bet.DataFlowName,"
    + "   bet.SchemaName,"
    + "   bet.SourceSystemName,"
    + "   bet.SelectQuery,"
    + "   bet.SQLFlavour,"
    + "   bet.ExtractTableName,"
    + "   bet.SourceTableName,"
    + "   bet.ArchiveProcedure"
    + " FROM META.BimlExtractArchivePackages AS bet       ";
 

 tblExtractTables = getData(query);

 foreach (DataRow row in tblExtractTables.Rows)
 {
  extractTables.Add(fillExtractTable(row[0].ToString(), row[1].ToString(), row[2].ToString(), row[3].ToString(), row[4].ToString(), row[5].ToString(), row[6].ToString(), row[7].ToString(), row[8].ToString(), row[9].ToString()));
 }


    
    foreach (ExtractTable extractTable in extractTables)
    {
        string text = System.IO.File.ReadAllText(@"C:\TFS\Temp\biml.txt");
        text = text.Replace("<#=extractTable.PackageName#>", extractTable.PackageName);
        text = text.Replace("<#= extractTable.SelectQuery#>", extractTable.SelectQuery);
        Clipboard.SetText(text);

   
    }   
    }

}

The most important thing to note about this code is the fact a predefined piece of biml code is loaded from a text file and the variables are replaced by the values coming from the meta data repository in SQL. The resulting non dynamic biml code can then be pasted in VS2008 or later with BIDS helper to generate the package. Any error messages will be a lot easier to debug. Will be continued..