Skip to main content

Extracting a Database Table to a Text File

What This Pattern Produces​

A package generated from this Biml has one dataflow that reads from a SQL Server table over OLE DB and writes the result to a delimited text file. The novelty compared to a designer-built package is that the file format has to be declared explicitly in Biml, since there is no destination wizard to infer it.

Skeleton​

The Biml document has three named children inside the root: 'Connections', 'FileFormats', and 'Packages'.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <!-- connections -->
  </Connections>
  <FileFormats>
    <!-- flat file format -->
  </FileFormats>
  <Packages>
    <Package Name="CopyLedger" ConstraintMode="Linear">
      <!-- dataflow -->
    </Package>
  </Packages>
</Biml>

Connections​

An 'OleDbConnection' to SQL Server pairs with a 'FlatFileConnection' that points at the output path. The flat file connection requires a 'FileFormat' attribute that names a format defined elsewhere in the document:

<Connections>
  <OleDbConnection Name="Source" ConnectionString="Provider=SQLOLEDB;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=OperationsDW" />
  <FlatFileConnection Name="Target" FilePath="F:\\Output.txt" FileFormat="LedgerTable" />
</Connections>

File Format​

The 'FlatFileFormat' element declares the column shape of the text file. Each 'Column' needs a name and a delimiter. The last column uses 'LF' so each row ends on its own line:

<FileFormats>
  <FlatFileFormat Name="LedgerTable" ColumnNamesInFirstDataRow="true" RowDelimiter="LF">
    <Columns>
      <Column Name="LedgerKey" Delimiter="Comma" />
      <Column Name="LedgerCodeAlternateKey" Delimiter="Comma" />
      <Column Name="LedgerDescription" Delimiter="Comma" />
      <Column Name="LedgerType" Delimiter="Comma" />
      <Column Name="Operator" Delimiter="LF" />
    </Columns>
  </FlatFileFormat>
</FileFormats>

Column names must match the column names returned by the source query. Mapping is by name and order does not matter.

Package and Dataflow​

The package contains one dataflow with an OLE DB source and a flat file destination. The destination references the previously declared connection by name, and 'Overwrite' set to true replaces any existing file:

<Package Name="Copy_Dim_Ledger" ConstraintMode="Linear">
  <Tasks>
    <Dataflow Name="Copy_Dim_Ledger">
      <Transformations>
        <OleDbSource Name="Src_Dim_Ledger" ConnectionName="Source">
          <DirectInput>Select LedgerKey, LedgerCodeAlternateKey, LedgerDescription, LedgerType, Operator from DimLedger;</DirectInput>
        </OleDbSource>
        <FlatFileDestination Name="Dest_Dim_Ledger" ConnectionName="Target" Overwrite="true" />
      </Transformations>
    </Dataflow>
  </Tasks>
</Package>

The 'ConstraintMode' attribute on the package controls control flow precedence. Inside a dataflow, the engine creates the precedence between transformations automatically based on the order they appear in the source.

Complete Example​

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <OleDbConnection Name="Source" ConnectionString="Provider=SQLOLEDB;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=OperationsDW" />
    <FlatFileConnection Name="Target" FilePath="F:\\Output.txt" FileFormat="LedgerTable" />
  </Connections>

  <FileFormats>
    <FlatFileFormat Name="LedgerTable" ColumnNamesInFirstDataRow="true" RowDelimiter="LF">
      <Columns>
        <Column Name="LedgerKey" Delimiter="Comma" />
        <Column Name="LedgerCodeAlternateKey" Delimiter="Comma" />
        <Column Name="LedgerDescription" Delimiter="Comma" />
        <Column Name="LedgerType" Delimiter="Comma" />
        <Column Name="Operator" Delimiter="LF" />
      </Columns>
    </FlatFileFormat>
  </FileFormats>

  <Packages>
    <Package Name="Copy_Dim_Ledger" ConstraintMode="Linear">
      <Tasks>
        <Dataflow Name="Copy_Dim_Ledger">
          <Transformations>
            <OleDbSource Name="Src_Dim_Ledger" ConnectionName="Source">
              <DirectInput>Select LedgerKey, LedgerCodeAlternateKey, LedgerDescription, LedgerType, Operator from DimLedger;</DirectInput>
            </OleDbSource>
            <FlatFileDestination Name="Dest_Dim_Ledger" ConnectionName="Target" Overwrite="true" />
          </Transformations>
        </Dataflow>
      </Tasks>
    </Package>
  </Packages>
</Biml>

Summary​

The minimum requirements are an OLE DB connection, a flat file connection that names a format, a 'FlatFileFormat' that lists columns matching the source query, and a dataflow that pairs the source and destination by name. Mapping is automatic by column name.