Union All from Two SQL Sources to a Flat File

Defines a 'SportsSurveyFormat' flat-file format with four semicolon-delimited columns (ID, Sport, Team, City), then declares a 'FlatFileConnection' pointed at that format. The 'My Dataflow Task' inside 'My Package' issues two 'select *' queries against 'SurveyResponses' and 'JuniorSurveyResponses' through the same 'SportsData' OLE DB connection, feeds them into a 'UnionAll' transformation, and writes the combined output to 'sportsOutput.txt' via the FlatFileDestination.

Adjust the 'SportsData' connection string and the output 'FilePath' to fit your environment. The 'UnionAll' relies on the two source queries returning compatible column metadata, so make sure both tables expose the four columns named in the file format.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <OleDbConnection Name="SportsData" ConnectionString="Provider=SQLNCLI10.1;Data Source=Localhost;Persist Security Info=False;Integrated Security=SSPI;Initial Catalog=SportsSurvey" />
    <FlatFileConnection Name="FlatFileConnection" FilePath="C:\users\paul\desktop\sportsOutput.txt" FileFormat="SportsSurveyFormat" />
  </Connections>
  <FileFormats>
    <FlatFileFormat Name="SportsSurveyFormat">
      <Columns>
        <Column Name="ID" Delimiter="Semicolon" TextQualified="false" />
        <Column Name="Sport" Delimiter="Semicolon" DataType="StringFixedLength" Length="40" />
        <Column Name="Team" Delimiter="Semicolon" DataType="StringFixedLength" Length="60" />
        <Column Name="City" DataType="StringFixedLength" Length="100" Delimiter="Semicolon" />
      </Columns>
    </FlatFileFormat>
  </FileFormats>
  <Packages>
    <Package Name="My Package" ConstraintMode="Linear">
      <Tasks>
        <Dataflow Name="My Dataflow Task">
          <Transformations>
            <OleDbSource Name="SurveyResponses" ConnectionName="SportsData">
              <DirectInput>select * from SurveyResponses</DirectInput>
            </OleDbSource>

            <OleDbSource Name="JuniorSurveyResponses" ConnectionName="SportsData">
              <DirectInput>select * from JuniorSurveyResponses</DirectInput>
            </OleDbSource>

            <!-- Does a union of the SurveyResponses and JuniorSurveyResponses to the flat file specified by FlatFileConnection. -->
            <UnionAll Name="Union All Transformation">
              <InputPaths>
                <InputPath OutputPathName="SurveyResponses.Output" />
                <InputPath OutputPathName="JuniorSurveyResponses.Output" />
              </InputPaths>
            </UnionAll>

            <FlatFileDestination Name="OutputFile" ConnectionName="FlatFileConnection" />
          </Transformations>
        </Dataflow>
      </Tasks>
    </Package>
  </Packages>
</Biml>