CODEWITHSUNDEEP
ssisbidsbiml
arcee123
arcee123

Reputation: 243

using external source to feed into BIML

Is there a way to reference a csv or other data source which has table names and source queries to generate BIML documents?

Thanks

Upvotes: 0

Views: 70

Answers (1)

iamdave
iamdave

Reputation: 12243

Do be aware C# is not my strong suit and the below may well not be the ideal way to do this. If you do find something more suitable, I would very much like to hear about it :)

The easiest way I have found to include CSV based metadata into my Biml projects is to load them into C# DataTable objects that I then reference in my Biml as a C# variable object, which plays very well with foreach to iterate through the rows.

Assuming you are aware how to include C# in your Biml projects (either in the file directly or via referenced .cs file), you can use the following code:

public static DataTable FlatFileToDataTable(string filePath, char delimiter)
{
    DataTable dt = new DataTable();

    using (StreamReader sr = new StreamReader(filePath))
    {
        string[] headers = sr.ReadLine().Split(delimiter);

        foreach (string header in headers)
        {
            dt.Columns.Add(header);
        }
        while (!sr.EndOfStream)
        {
            string[] rows = sr.ReadLine().Split(delimiter);
            DataRow dr = dt.NewRow();
            for (int i = 0; i < headers.Length; i++)
            {
                dr[i] = rows[i];
            }
            dt.Rows.Add(dr);
        }
    }
    return dt;
}

I think in order to use the StreamReader you will need to add using System.IO; to your code file as well.

Usage would be to define a DataTable object and populate it with the result of the above, then to reference it using code snippets within your Biml:

DataTable YourDataTable = FlatFileToDataTable("<Path to CSV file>",'<Value Delimiter>');

...

<Columns>
<#  foreach(DataRow r in YourDataTable.Rows){ #>
    <Column Name="<#=r["YourColumnName"]#>" etc />
<#  } #>
</Columns>

Upvotes: 1

Related Questions