Export to Flatfiles with Biml

Export to Flatfiles with Biml

Diesen Artikel finden Sie hier auch in deutscher Sprache.

After looking at importing data from flatfiles in some previous posts, it is time to export data into flat files using Biml!

First, we point Biml towards our AdventureWorks database:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
		<Connections>
        	<OleDbConnection Name="Source" ConnectionString="Data Source=localhost;initial catalog=Adventureworks2014;provider=SQLNCLI11;integrated security=SSPI"></OleDbConnection>
		</Connections>
</Biml>

In our next step, we loop through all tables in that database (feel free to limit the results by playing with GetDatabaseSchema) and create a FlatFileFormat for each of them. We will include all columns except those with datatype Binary or Object. As flatfiles don’t really care about actual data formats, we will just define every column as a string with maximum length. We will also add an annotation with the table’s original name, the list of columns as well as a list of primary keys (we’ll need the latter for a later step :)):

<#@ template language="VB" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <FileFormats>
        <# Dim SourceConnection as AstDbConnectionNode  = RootNode.Connections("Source") 
	   Dim Delimiter as string 
       Dim Columns as string
	   Dim KeyColumns as string
   Dim SrcTableResults as ImportResults = SourceConnection.GetDatabaseSchema
   for each tbl as asttablenode in srctableresults.tablenodes 
   Columns =""
   KeyColumns = ""#>
        <FlatFileFormat Name="<#= tbl.schema  #>_<#= tbl.name #>" ColumnNamesInFirstDataRow="true">
            <Columns>
                <# for each col as asttablecolumnbasenode in tbl.columns.where(function(c) not (c.Datatype = system.data.dbtype.Binary or c.Datatype = system.data.dbtype.Object) ) 
                   if Columns <> "" then columns = Columns + ", "
                   columns = columns + "[" + col.name + "]"
				   if col.IsUsedInPrimaryKey = true then
				   		if KeyColumns <> "" then KeyColumns = KeyColumns + ", "
                   		KeyColumns = KeyColumns + "[" + col.name + "]"
				   end if
				   Delimiter =","
				   if col.name = tbl.columns.last.name then Delimiter = "CRLF"  #>
                <Column Name="<#= col.name #>" DataType="String" Length="-1" Delimiter="<#= delimiter #>"/>
                  <# next 
					 if KeyColumns = "" then KeyColumns = "1"#>
            </Columns>
            <Annotations>
                <Annotation AnnotationType="Tag" Tag="Sourcetable"><#= tbl.scopedname #></Annotation>
                <Annotation AnnotationType="Tag" Tag="Sourcecolumns"><#= columns  #></Annotation>
				<Annotation AnnotationType="Tag" Tag="Keycolumns"><#= KeyColumns  #></Annotation>
            </Annotations>
        </FlatFileFormat>
        <# next #>
    </FileFormats>
</Biml>

As you know, to work with flat files in Biml, we need a format (which we’ve just defined) as well as a connection. We’ll just create one connection for each format by simply looping through them:

<#@ template language="VB" tier="2" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Connections>
	<# for each ff as AstFlatFileFormatNode in rootnode.flatfileformats #>
		<FlatFileConnection Name="<#=ff.name #>" FileFormat="<#=ff #>" FilePath="C:\Flatfiles\Output\<#=ff.name #>.csv"  />
	<# next #>
</Connections>
</Biml>

Our last step is to actually export the data. We will do that all in one package, dumping out all tables in parallel:

<#@ template language="VB" tier="3" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
	<Packages>
		<Package Name="Export_Flatfiles">
			<Tasks>
			<# for each ff as AstFlatFileFormatNode in rootnode.flatfileformats #>
				<Container Name="Export <#=ff #>">
					<Tasks>
						<Dataflow Name="DF <#=ff #>">
							<Transformations>
								<OleDbSource Name="SRC <#=ff #>" ConnectionName="Source">
									<DirectInput>SELECT <#= ff.gettag("Sourcecolumns") #> FROM <#= ff.gettag("Sourcetable") #></DirectInput>
                                </OleDbSource>
								<FlatFileDestination ConnectionName="<#= ff #>" Name="<#=ff #>" />								
                            </Transformations>
						</Dataflow>
                    </Tasks>
				</Container>
			<# next #>
			</Tasks>
        </Package>
    </Packages>
</Biml>

This will result in one CSV per table in AdventureWorks!

Any questions or comments? We’d love to hear from you at biml@solisyon.de!

Further Information on Biml in English can be found on our English Biml Page.

Happy Biml’ing!

Facebook