Biml Export to Flat File

Queries 'INFORMATION_SCHEMA.TABLES' and 'INFORMATION_SCHEMA.COLUMNS' on the source server via 'ExternalDataAccess.GetDataTable' and walks every base table in the chosen schema. For each table the script emits a matching 'FlatFileFormat' (one 'Column' per source column, with semicolon delimiters between fields and CRLF after the last column), a 'FlatFileConnection' that points at 'C:\catalog.schema.table.dat', and a 'Dataflow' inside a single 'Extract.All' package that copies the table through an 'OleDbSource' to a 'FlatFileDestination'.

Set 'CnSrcDB', 'SrcTableCatalog', and 'SrcTableSchema' at the top of the script to match the server, database, and schema you want to export. The output flat files are written to the root of C:\ by default; change the 'FilePath' template if you want them somewhere else. All exported columns are declared as 4000-character strings, which mirrors what a CSV file actually stores.

<#@ 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">
  <#
  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>

---

Submitted by John Minkjan.