BIML Export to FlatFile

Demo BIML on creating an export to flat file package based on the SQL-Server metadata views:
<#@ template language="C#" hostspecific="true"#>
<#@ import namespace="System.Data"#>
<#@ import namespace="System.Data.SqlClient"#>
<#@ import namespace="System.IO"#>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Annotations>
        <Annotation>
            File: Export.DataBase.Schema.biml
            Demo make a export to file package
            Enviroment:
            DB: MS-SQL2012
            BIML: 1.6 VS2010 BIDS Helper
            (c) John Minkjan biml101.blogspot.com
        </Annotation>
    </Annotations>
<#
    string CnSrcDB = "Server=.\\MSSQL2012;Initial Catalog=AdventureWorks2012;Integrated Security=SSPI;Provider=SQLNCLI11.1";
    string SrcTableCatalog = "AdventureWorks2012";
    string SrcTableSchema ="Sales";
    string TableCatalog="";
    string TableSchema ="";
    string TableName ="";
    string ColumnName ="";
    string DataType ="";
    string CharacterMaximumLength ="";
    string FlatFileFormatName ="";
    string MaxOrdinalPosition ="";
    string ColumnDelimeter =";";
   
    DataTable tFFFCol;
#>
<FileFormats>
    <# DataTable tFFF = ExternalDataAccess.GetDataTable(CnSrcDB,
    "SELECT t1.TABLE_CATALOG, t1.TABLE_SCHEMA, t1.TABLE_NAME,  max(t1.ORDINAL_POSITION) as MAX_ORDINAL_POSITION FROM  INFORMATION_SCHEMA.COLUMNS AS t1 INNER JOIN INFORMATION_SCHEMA.TABLES  AS t2 ON t1.TABLE_CATALOG = t2.TABLE_CATALOG  AND t1.TABLE_SCHEMA = t2.TABLE_SCHEMA AND t1.TABLE_NAME = t2.TABLE_NAME  WHERE        (t1.TABLE_CATALOG = '" +SrcTableCatalog + "') AND (t2.TABLE_TYPE = 'BASE TABLE')  and t2.TABLE_SCHEMA = '" + SrcTableSchema +"' group by t1.TABLE_CATALOG, t1.TABLE_SCHEMA, t1.TABLE_NAME");
    foreach (DataRow tFFFr in tFFF.Rows){
    TableCatalog =tFFFr[0].ToString();
    TableSchema = tFFFr[1].ToString();
    TableName = tFFFr[2].ToString();
    MaxOrdinalPosition = tFFFr[3].ToString();
    #>
    <FlatFileFormat FlatFileType="Delimited"     ColumnNamesInFirstDataRow="true"     RowDelimiter="CRLF"  Name="FFF <#=TableCatalog#> <#=TableSchema#> <#=TableName#>">
        <Columns>
            <# tFFFCol = ExternalDataAccess.GetDataTable(CnSrcDB,
            "SELECT t1.COLUMN_NAME, t1.DATA_TYPE, t1.CHARACTER_MAXIMUM_LENGTH, T1.ORDINAL_POSITION FROM INFORMATION_SCHEMA.COLUMNS AS t1 where t1.TABLE_CATALOG = '"+TableCatalog+"' and t1.TABLE_SCHEMA = '"+TableSchema+"' and t1.TABLE_NAME = '"+TableName+"'");
            foreach (DataRow tFFFRow in tFFFCol.Rows){
            ColumnName =tFFFRow[0].ToString();
            DataType = tFFFRow[1].ToString();
            CharacterMaximumLength = tFFFRow[2].ToString();   
                ColumnDelimeter = ";";
                if (MaxOrdinalPosition == tFFFRow[3].ToString()){ ColumnDelimeter = "CRLF"; } ;
                DataType = "String";                               
            #>
                <Column ColumnType="Delimited" DataType= "<#=DataType#>" Delimiter="<#=ColumnDelimeter#>" Length="4000" Name="<#=ColumnName#>"></Column>
            <# } #>
        </Columns>
    </FlatFileFormat>
    <#}  #>
</FileFormats>
    <Connections>
        <OleDbConnection
            Name="CnOleDBAdventureWorks2012"
            ConnectionString="Data Source=.\MSSQL2012;Initial Catalog=AdventureWorks2012;Provider=SQLNCLI10.1;Integrated Security=SSPI;Connect Timeout=30;"
            RetainSameConnection="true">
        </OleDbConnection>
        <# DataTable tFF = ExternalDataAccess.GetDataTable(CnSrcDB,
        "SELECT t1.TABLE_CATALOG, t1.TABLE_SCHEMA, t1.TABLE_NAME FROM  INFORMATION_SCHEMA.COLUMNS AS t1 INNER JOIN INFORMATION_SCHEMA.TABLES  AS t2 ON t1.TABLE_CATALOG = t2.TABLE_CATALOG  AND t1.TABLE_SCHEMA = t2.TABLE_SCHEMA AND t1.TABLE_NAME = t2.TABLE_NAME  WHERE        (t1.TABLE_CATALOG = '"
        +SrcTableCatalog+"') AND (t2.TABLE_TYPE = 'BASE TABLE') and t2.TABLE_SCHEMA = '"
        +SrcTableSchema+"' group by t1.TABLE_CATALOG, t1.TABLE_SCHEMA, t1.TABLE_NAME");
        foreach (DataRow tFFFr in tFFF.Rows){
        TableCatalog =tFFFr[0].ToString();
        TableSchema = tFFFr[1].ToString();
        TableName = tFFFr[2].ToString(); #>
            <FlatFileConnection Name="FF <#=TableCatalog#> <#=TableSchema#> <#=TableName#>"
                FileFormat="FFF <#=TableCatalog#> <#=TableSchema#> <#=TableName#>"
                FilePath="C:\\<#=TableCatalog#>.<#=TableSchema#>.<#=TableName#>.dat"></FlatFileConnection>           
            <# }#>
    </Connections>
    <Packages>
        <Package Name="Extract.All" ConstraintMode="Linear" FailParentOnFailure="false">
            <Tasks>
                <#    
DataTable t1 = ExternalDataAccess.GetDataTable(CnSrcDB,
"SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES AS t2 WHERE (TABLE_CATALOG = '"
+SrcTableCatalog +"') AND (TABLE_TYPE = 'BASE TABLE') and t2.TABLE_SCHEMA = '"+SrcTableSchema+"' ");
foreach (DataRow row1 in t1.Rows){
    TableCatalog =row1[0].ToString();
    TableSchema = row1[1].ToString();
    TableName = row1[2].ToString(); #>
                <Dataflow  Name="DFT <#=TableCatalog#> <#=TableSchema#> <#=TableName#>">
                    <Transformations>
                        <OleDbSource
                            Name="ODS <#=TableCatalog#> <#=TableSchema#> <#=TableName#>"
                            ConnectionName="CnOleDBAdventureWorks2012">
                            <ExternalTableInput Table ="[<#=TableCatalog#>].[<#=TableSchema#>].[<#=TableName#>]">
                            </ExternalTableInput>
                        </OleDbSource>
                        <FlatFileDestination Name="DFF <#=TableCatalog#> <#=TableSchema#> <#=TableName#>"
                            ConnectionName="FF <#=TableCatalog#> <#=TableSchema#> <#=TableName#>"></FlatFileDestination>                       
                    </Transformations>
                </Dataflow>
                <# } #>
            </Tasks>
        </Package>
    </Packages>
</Biml>
Gives you:

Till Next Time