BIML Select a DataFlow based on FileHeader

On a project the source file where delivered in one and same directory, but having a variety of headers:

First I catch the the column string of the first row and lead it via a conditional split to a row count:

Based on the row count I run a specific dataflow task:

Here is a way to solve it using BIML:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Annotations>
        <Annotation>
            File: Dataflow.Select.biml
            Demo on selection a dataflow based on the file header
            Enviroment:
            DB: MS-SQL2012
            BIML: 1.6 VS2010 BIDS Helper
            (c) John Minkjan biml101.blogspot.com
        </Annotation>
    </Annotations>
    <FileFormats>
        <FlatFileFormat Name="FFF CheckFile" IsUnicode ="false" RowDelimiter ="CRLF" ColumnNamesInFirstDataRow ="false">
            <Columns>
                <Column Name="FileInfo" DataType="String" CodePage ="1252" Length="250" Delimiter="CRLF"></Column>
            </Columns>
        </FlatFileFormat>
        <FlatFileFormat Name="FFF Type1" IsUnicode ="false" RowDelimiter ="CRLF" ColumnNamesInFirstDataRow ="true" >
            <Columns>
                <Column Name="PersId" DataType="String" CodePage ="1252" Length="250" Delimiter=";"></Column>
                <Column Name="FirstName" DataType="String" CodePage ="1252" Length="250" Delimiter="CRLF"></Column>
            </Columns>
        </FlatFileFormat>
        <FlatFileFormat Name="FFF Type2" IsUnicode ="false" RowDelimiter ="CRLF" ColumnNamesInFirstDataRow ="true" >
            <Columns>
                <Column Name="PersId" DataType="String" CodePage ="1252" Length="250" Delimiter=";"></Column>
                <Column Name="LastName" DataType="String" CodePage ="1252" Length="250" Delimiter="CRLF"></Column>
            </Columns>
        </FlatFileFormat>
        <FlatFileFormat Name="FFF Type3" IsUnicode ="false" RowDelimiter ="CRLF" ColumnNamesInFirstDataRow ="true" >
            <Columns>
                <Column Name="PersId" DataType="String" CodePage ="1252" Length="250" Delimiter=";"></Column>
                <Column Name="FirstName" DataType="String" CodePage ="1252" Length="250" Delimiter=";"></Column>
                <Column Name="LastName" DataType="String" CodePage ="1252" Length="250" Delimiter="CRLF"></Column>
            </Columns>
        </FlatFileFormat>

    </FileFormats>
    <Connections>
        <FlatFileConnection Name ="FFC SourceFile" FileFormat ="FFF CheckFile" FilePath ="C:\\DEMO\\PERSON1.TXT">
            <Expressions>
                <Expression PropertyName="ConnectionString">@[User::ProcessFileName]</Expression>
            </Expressions>           
        </FlatFileConnection>
        <FlatFileConnection Name ="FFC Type1" FileFormat ="FFF Type1" FilePath ="C:\\DEMO\\PERSON1.TXT">
            <Expressions>
                <Expression PropertyName="ConnectionString">@[User::ProcessFileName]</Expression>
            </Expressions>
        </FlatFileConnection>
        <FlatFileConnection Name ="FFC Type2" FileFormat ="FFF Type2" FilePath ="C:\\DEMO\\PERSON2.TXT">
            <Expressions>
                <Expression PropertyName="ConnectionString">@[User::ProcessFileName]</Expression>
            </Expressions>
        </FlatFileConnection>
        <FlatFileConnection Name ="FFC Type3" FileFormat ="FFF Type3" FilePath ="C:\\DEMO\\PERSON3.TXT">
            <Expressions>
                <Expression PropertyName="ConnectionString">@[User::ProcessFileName]</Expression>
            </Expressions>
        </FlatFileConnection>
        <OleDbConnection   
            Name="CnOleDBAdventureWorks2012"
            ConnectionString="Data Source=.\MSSQL2012;Initial Catalog=AdventureWorks2012;Provider=SQLNCLI10.1;Integrated Security=SSPI;Connect Timeout=30;"/>
    </Connections>
    <Packages>
        <Package Name="Dataflow.Select" ConstraintMode="Linear">
            <Variables>
                <Variable Name="SourceFileLocation" DataType ="String">C:\DEMO\</Variable>
                <Variable Name="FilePrefix"    DataType="String">Person</Variable>
                <Variable Name="FileSuffix" DataType="String">txt</Variable>
                <Variable Name="ProcessFileName" DataType ="String">C:\DEMO\Person3.txt</Variable>
               
            </Variables>
            <Tasks>
                <ForEachFileLoop Name="FLC File Select" ConstraintMode ="Parallel"
                                 Folder ="C:\DEMO\" FileSpecification="Person*.txt"  >
                    <VariableMappings>
                        <VariableMapping Name="Mapping" VariableName="User.ProcessFileName" />
                    </VariableMappings>
                    <Expressions>
                        <Expression PropertyName="Directory">@[User::SourceFileLocation]</Expression>
                        <Expression PropertyName="FileSpec">@[User::FilePrefix]+"*."+ @[User::FileSuffix]</Expression>
                    </Expressions>
                    <Variables>
                        <Variable Name="RowCountType1" DataType="Int64">0</Variable>
                        <Variable Name="RowCountType2" DataType="Int64">0</Variable>
                        <Variable Name="RowCountType3" DataType="Int64">0</Variable>
                    </Variables>
                    <Tasks>
                        <Dataflow Name="DFT Get File Header">
                            <Transformations>
                                <FlatFileSource Name="FFC SourceFile" ConnectionName ="FFC SourceFile">
                                    <Columns>
                                        <Column SourceColumn="FileInfo" TargetColumn ="FileInfo"></Column>
                                    </Columns>
                                </FlatFileSource>
                                <ConditionalSplit Name="CS FileType">
                                    <OutputPaths>
                                        <OutputPath Name ="Type1">
                                            <Expression>[FileInfo] =="PersId;FirstName"</Expression>
                                        </OutputPath>
                                        <OutputPath Name ="Type2">
                                            <Expression>[FileInfo] =="PersId;LastName"</Expression>
                                        </OutputPath>
                                        <OutputPath Name ="Type3">
                                            <Expression>[FileInfo] =="PersId;FirstName;LastName"</Expression>
                                        </OutputPath>
                                    </OutputPaths>                                   
                                </ConditionalSplit>
                                <RowCount Name ="RC Type1" VariableName="User.RowCountType1">
                                    <InputPath OutputPathName="CS FileType.Type1"></InputPath>
                                </RowCount>
                                <RowCount Name ="RC Type2" VariableName="User.RowCountType2">
                                    <InputPath OutputPathName="CS FileType.Type2"></InputPath>
                                </RowCount>
                                <RowCount Name ="RC Type3" VariableName="User.RowCountType3">
                                    <InputPath OutputPathName="CS FileType.Type3"></InputPath>
                                </RowCount>
                            </Transformations>
                        </Dataflow>
                        <Dataflow Name="DFT Type1">
                            <PrecedenceConstraints>
                                <Inputs>
                                    <Input EvaluationOperation ="ExpressionAndConstraint"
                                           EvaluationValue ="Success"
                                           Expression ="@RowCountType1!=0"
                                           OutputPathName ="DFT Get File Header.Output"></Input>
                                </Inputs>
                            </PrecedenceConstraints>
                            <Transformations>
                                <FlatFileSource Name="FFC Type1" ConnectionName ="FFC Type1"></FlatFileSource>
                                <OleDbDestination Name="ODD Persons" ConnectionName="CnOleDBAdventureWorks2012">
                                    <ExternalTableOutput Table ="Persons"></ExternalTableOutput>
                                </OleDbDestination>
                            </Transformations>
                        </Dataflow>
                        <Dataflow Name="DFT Type2">
                            <PrecedenceConstraints>
                                <Inputs>
                                    <Input EvaluationOperation ="ExpressionAndConstraint"
                                           EvaluationValue ="Success"
                                           Expression ="@RowCountType2!=0"
                                           OutputPathName ="DFT Get File Header.Output"></Input>
                                </Inputs>
                            </PrecedenceConstraints>
                            <Transformations>
                                <FlatFileSource Name="FFC Type2" ConnectionName ="FFC Type2"></FlatFileSource>
                                <OleDbDestination Name="ODD Persons" ConnectionName="CnOleDBAdventureWorks2012">
                                    <ExternalTableOutput Table ="Persons"></ExternalTableOutput>
                                </OleDbDestination>
                            </Transformations>
                        </Dataflow>
                        <Dataflow Name="DFT Type3">
                            <PrecedenceConstraints>
                                <Inputs>
                                    <Input EvaluationOperation ="ExpressionAndConstraint"
                                           EvaluationValue ="Success"
                                           Expression ="@RowCountType3!=0"
                                           OutputPathName ="DFT Get File Header.Output"></Input>
                                </Inputs>
                            </PrecedenceConstraints>
                            <Transformations>
                                <FlatFileSource Name="FFC Type3" ConnectionName ="FFC Type3"></FlatFileSource>
                                <OleDbDestination Name="ODD Persons" ConnectionName="CnOleDBAdventureWorks2012">
                                    <ExternalTableOutput Table ="Persons"></ExternalTableOutput>
                                </OleDbDestination>
                            </Transformations>
                        </Dataflow>
                    </Tasks>
                </ForEachFileLoop>
            </Tasks>
           
        </Package>
    </Packages>
</Biml>

This will get you:

Till Next Time

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

BIML Include

On of the big risks is that BIML file have a tendency to become “LARGE” and thus become unreadable. Luckily you can use the Include directive to cut you BIML into readable blocks. For me a rule of thumb is a 100 line or less. Based on the script I did here: http://biml101.blogspot.nl/2012/09/biml-add-row-number-to-file-export.html I brought the Flat File definitions and the scripcompent C# part to external txt files. This also increases the reusability of code.!
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Annotations>
        <Annotation>
            File: Add.RowNumber.Include.biml
            Demo on using the Include file
            Enviroment:
            DB: MS-SQL2012
            BIML: 1.6 VS2010 BIDS Helper
            (c) John Minkjan biml101.blogspot.com
        </Annotation>
    </Annotations>
    <FileFormats>
        <#@ include file="C:\BIML\FFF\FFF AdventureWorks2012 Person Address.txt" #>
    </FileFormats>
    <Connections>
        <#@ include file="C:\BIML\CN\CnOleDBAdventureWorks2012.txt" #>           
        <FlatFileConnection Name ="CnFFAdventureWorks2012PersonAddress"
                            FileFormat ="FFF AdventureWorks2012 Person Address"
                            FilePath="C:\AdventureWorks2012.Person.Address.dat"></FlatFileConnection>
    </Connections>
    <Packages>
        <Package Name="Add.RowNumber.Include" ConstraintMode="Linear">
            <Tasks>
                <Dataflow Name="DFT Add Rownumber">
                    <Transformations>
                        <OleDbSource Name ="ODS AdventureWorks2012 Person Address"
                                     ConnectionName ="CnOleDBAdventureWorks2012">
                            <DirectInput>
                                SELECT [AddressID]
                                ,[AddressLine1]
                                ,[AddressLine2]
                                ,[City]
                                ,[StateProvinceID]
                                ,[PostalCode]
                                ,[rowguid]
                                ,[ModifiedDate]
                                FROM [AdventureWorks2012].[Person].[Address]
                            </DirectInput>                       
                        </OleDbSource>
                        <DerivedColumns Name="DC ADD RowNumber Column">
                            <Columns>
                                <Column Name="RowNumber" DataType ="Int64">0</Column>
                            </Columns>
                        </DerivedColumns>
                        <ScriptComponentTransformation Name ="SC Add RowNumber">
                            <ScriptComponentProject>
                                <#@ include file="C:\BIML\SC\SCP AddRowNumber.txt" #>
                            </ScriptComponentProject>
                        </ScriptComponentTransformation>
                       
                        <FlatFileDestination Name ="FFD AdventureWorks2012 Person Address"
                                             ConnectionName ="CnFFAdventureWorks2012PersonAddress"
                                             Overwrite ="true">
                       
                        </FlatFileDestination>
                    </Transformations>
                </Dataflow>
            </Tasks>
        </Package>
    </Packages>
</Biml>
Till Next Time

BIML Add Row number to File Export

I like to add a row number to the flat file exports. This helps a lot in the communication with business when a problem arrives. This is an example on how to add a row number:
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Annotations>
        <Annotation>
            File: Add.RowNumber.biml
            Demo biml adding a rownumber to FileExport
            Enviroment:
            DB: MS-SQL2012
            BIML: 1.6 VS2010 BIDS Helper
            (c) John Minkjan biml101.blogspot.com
        </Annotation>
    </Annotations>
    <FileFormats>
        <FlatFileFormat
            Name ="FFF AdventureWorks2012 Person Address"
            ColumnNamesInFirstDataRow="true"
            FlatFileType="Delimited"
            HeaderRowDelimiter=";"
            RowDelimiter="CRLF"
            TextQualifer="|">
            <Columns>
                <Column Name="RowNumber" DataType ="String" Length="38" Delimiter =";"></Column>
                <Column Name="AddressID" DataType ="String" Length="128" Delimiter =";"></Column>
                <Column Name="AddressLine1" DataType ="String" Length="128" Delimiter =";"></Column>
                <Column Name="AddressLine2" DataType ="String" Length="128" Delimiter =";"></Column>
                <Column Name="City" DataType ="String" Length="128" Delimiter =";"></Column>
                <Column Name="StateProvinceID" DataType ="String" Length="128" Delimiter =";"></Column>
                <Column Name="PostalCode" DataType ="String" Length="128" Delimiter =";"></Column>
                <Column Name="rowguid" DataType ="String" Length="128" Delimiter =";"></Column>
                <Column Name="ModifiedDate" DataType ="String" Length="128" Delimiter ="CRLF"></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>
        <FlatFileConnection Name ="FF AdventureWorks2012 Person Address"
                            FileFormat ="FFF AdventureWorks2012 Person Address"
                            FilePath="C:\AdventureWorks2012.Person.Address.dat"></FlatFileConnection>
    </Connections>
    <Packages>
        <Package Name="Add.RowNumber" ConstraintMode="Linear">
            <Tasks>
                <Dataflow Name="DFT Add Rownumber">
                    <Transformations>
                        <OleDbSource Name ="ODS AdventureWorks2012 Person Address"
                                     ConnectionName ="CnOleDBAdventureWorks2012">
                            <DirectInput>
                                SELECT [AddressID]
                                ,[AddressLine1]
                                ,[AddressLine2]
                                ,[City]
                                ,[StateProvinceID]
                                ,[PostalCode]
                                ,[rowguid]
                                ,[ModifiedDate]
                                FROM [AdventureWorks2012].[Person].[Address]
                            </DirectInput>                       
                        </OleDbSource>
                        <DerivedColumns Name="DC ADD RowNumber Column">
                            <Columns>
                                <Column Name="RowNumber" DataType ="Int64">0</Column>
                            </Columns>
                        </DerivedColumns>
                        <ScriptComponentTransformation Name ="SC Add RowNumber">
                            <ScriptComponentProject>
                                <ScriptComponentProject Name="SC_AddRowNumber">
                                    <AssemblyReferences>
                                        <AssemblyReference AssemblyPath="Microsoft.SqlServer.DTSPipelineWrap" />
                                        <AssemblyReference AssemblyPath="Microsoft.SqlServer.DTSRuntimeWrap" />
                                        <AssemblyReference AssemblyPath="Microsoft.SqlServer.PipelineHost" />
                                        <AssemblyReference AssemblyPath="Microsoft.SqlServer.TxScript" />
                                        <AssemblyReference AssemblyPath="System.dll" />
                                        <AssemblyReference AssemblyPath="System.AddIn.dll" />
                                        <AssemblyReference AssemblyPath="System.Data.dll" />
                                        <AssemblyReference AssemblyPath="System.Xml.dll" />
                                    </AssemblyReferences>
                                    <InputBuffer Name="Input0">
                                        <Columns>
                                            <Column Name="RowNumber" DataType ="Int64" UsageType ="ReadWrite"></Column>
                                        </Columns>
                                    </InputBuffer>
                                    <OutputBuffers>
                                        <OutputBuffer Name="Output0"></OutputBuffer>
                                    </OutputBuffers>
                                    <Files>
                                        <File Path ="AssemblyInfo.cs">
                                            using System.Reflection;
                                            using System.Runtime.CompilerServices;
                                            [assembly: AssemblyTitle("SC_AddRowNumber")]
                                            [assembly: AssemblyDescription("")]
                                            [assembly: AssemblyConfiguration("")]
                                            [assembly: AssemblyCompany("")]
                                            [assembly: AssemblyProduct("SC_AddRowNumber")]
                                            [assembly: AssemblyCopyright("Copyright @  2012")]
                                            [assembly: AssemblyTrademark("")]
                                            [assembly: AssemblyCulture("")]
                                            [assembly: AssemblyVersion("1.0.*")]
                                        </File>
                                        <File Path ="main.cs">
                                            using System;
                                            using System.Data;
                                            using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
                                            using Microsoft.SqlServer.Dts.Runtime.Wrapper;
                                            [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
                                            public class ScriptMain : UserComponent
                                            {
                                                Int64 lRownumber = 0;
                                                public override void PreExecute()
                                                {
                                                base.PreExecute();
                                                }
                                                public override void PostExecute()
                                                {
                                                base.PostExecute();
                                                }
                                                public override void Input0_ProcessInputRow(Input0Buffer Row)
                                                {
                                                lRownumber = lRownumber + 1;
                                                Row.RowNumber =   lRownumber ;
                                                }
                                            }
                                        </File>
                                    </Files>
                                </ScriptComponentProject>
                            </ScriptComponentProject>
                           
                        </ScriptComponentTransformation>
                       
                        <FlatFileDestination Name ="FFD AdventureWorks2012 Person Address"
                                             ConnectionName ="FF AdventureWorks2012 Person Address"
                                             Overwrite ="true">
                       
                        </FlatFileDestination>
                    </Transformations>
                </Dataflow>
            </Tasks>
        </Package>
    </Packages>
</Biml>
This will get you:


Till Next Time

BIML MD5 ScriptComponent Hash

Demo biml using the ScriptComponent to create an MD5 hash, very handy if you are not allowed to use open source altenatives like ==> http://ssismhash.codeplex.com/
<#@ 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: MD5.ScriptComponent.biml
            Demo biml using the ScriptComponent to
            create an MD5 hash.
            Enviroment:
            DB: MS-SQL2012
            BIML: 1.6 VS2010 BIDS Helper
            (c) John Minkjan biml101.blogspot.com
        </Annotation>
</Annotations>
<Connections>
<OleDbConnection
    Name="CnOleDBAdventureWorks2012"
    ConnectionString="Data Source=.\MSSQL2012;Initial Catalog=AdventureWorks2012;Provider=SQLNCLI10.1;Integrated Security=SSPI;Connect Timeout=30;"/>
</Connections>
   
<ScriptProjects>                       
    <ScriptComponentProject ProjectCoreName="sc_c253bef215bf4d6b85dbe3919c35c167.csproj" Name="SC Add MD5">
        <AssemblyReferences>
            <AssemblyReference AssemblyPath="Microsoft.SqlServer.DTSPipelineWrap" />
            <AssemblyReference AssemblyPath="Microsoft.SqlServer.DTSRuntimeWrap" />
            <AssemblyReference AssemblyPath="Microsoft.SqlServer.PipelineHost" />
            <AssemblyReference AssemblyPath="Microsoft.SqlServer.TxScript" />
            <AssemblyReference AssemblyPath="System.dll" />
            <AssemblyReference AssemblyPath="System.AddIn.dll" />
            <AssemblyReference AssemblyPath="System.Data.dll" />
            <AssemblyReference AssemblyPath="System.Xml.dll" />
        </AssemblyReferences>
        <Files>
            <File Path="AssemblyInfo.cs">
            using System.Reflection;
            using System.Runtime.CompilerServices;
            [assembly: AssemblyTitle("SC_c253bef215bf4d6b85dbe3919c35c167.csproj")]
            [assembly: AssemblyDescription("")]
            [assembly: AssemblyConfiguration("")]
            [assembly: AssemblyCompany("Ciber Nederland")]
            [assembly: AssemblyProduct("SC_c253bef215bf4d6b85dbe3919c35c167.csproj")]
            [assembly: AssemblyCopyright("Copyright @ Ciber Nederland 2012")]
            [assembly: AssemblyTrademark("")]
            [assembly: AssemblyCulture("")]
            [assembly: AssemblyVersion("1.0.*")]
            </File>
            <File Path="ScriptMain.cs">
            using System;
            using System.Data;
            using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
            using Microsoft.SqlServer.Dts.Runtime.Wrapper;
            using System.Security.Cryptography;
            using System.Text;
            using System.IO;
            [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
            public class ScriptMain : UserComponent
            {
            static public string GetMd5Sum(string str)
            {
            Encoder enc = System.Text.Encoding.Unicode.GetEncoder();
            byte[] unicodeText = new byte[str.Length * 2];
            enc.GetBytes(str.ToCharArray(), 0, str.Length, unicodeText, 0, true);
            MD5 md5 = new MD5CryptoServiceProvider();
            byte[] result = md5.ComputeHash(unicodeText);
            StringBuilder sb = new StringBuilder();
            for (int i = 0; i != result.Length; i++)
            {
            sb.Append(result[i].ToString("X2"));
            }
            return sb.ToString();
            }
            public override void PreExecute()
            {
            base.PreExecute();
            }
            public override void PostExecute()
            {
            base.PostExecute();
            }
            public override void Input0_ProcessInputRow(Input0Buffer Row)
            {
            Row.MD5AllColumns = GetMd5Sum(Row.FirstName + Row.LastName);
            }
            }
            </File>
        </Files>
<InputBuffer Name="Input0">
    <Columns>
        <Column Name="MD5AllColumns" DataType="AnsiString" Length="50" UsageType="ReadWrite"></Column>
        <Column Name="FirstName" DataType="AnsiString" Length="50" UsageType="ReadWrite"></Column>
        <Column Name="LastName" DataType="AnsiString" Length="50" UsageType="ReadWrite"></Column>
    </Columns>
</InputBuffer>
    <OutputBuffers>
        <OutputBuffer Name="Output0">
    </OutputBuffer>
</OutputBuffers>
</ScriptComponentProject>
</ScriptProjects>
    <Packages>
        <Package  ConstraintMode="Parallel" Name="MD5.ScriptComponent">
        <Variables>
                <Variable Name ="RowCount"
                          DataType="Int32"
                          EvaluateAsExpression="false">0</Variable>
                <!--Stock symbol has to be enclosed in double quotes-->
            </Variables>
            <Tasks>
                <Container ConstraintMode ="Parallel" Name="SEQ Main">
                    <Tasks>
                        <Dataflow Name="DFT Main">
                            <Transformations>
                                <OleDbSource Name="ODS Adventureworks Person" ConnectionName="CnOleDBAdventureWorks2012">
                                    <ExternalTableInput Table = "[Person].[Person]"></ExternalTableInput>
                                </OleDbSource>
                                <DerivedColumns Name="DC MD5">
                                <InputPath OutputPathName="ODS Adventureworks Person.Output"></InputPath>
                                <Columns>
                                    <Column Name="MD5AllColumns" DataType="AnsiString" Length="50">"--"</Column>                                   
                                </Columns>
                            </DerivedColumns>
                                <ScriptComponentTransformation Name="SC Add MD5">
                                <InputPath OutputPathName="DC MD5.Output"></InputPath>
                                <ScriptComponentProjectReference ScriptComponentProjectName="SC Add MD5" />                               
                            </ScriptComponentTransformation>
                            <!-- Dummy Rowcount to show results in dataviewer -->
                            <RowCount VariableName="User.RowCount" Name="RC Input">
                                <InputPath OutputPathName="SC Add MD5.Output0"></InputPath>
                            </RowCount>
                            </Transformations>
                        </Dataflow>
                    </Tasks>
                </Container>
            </Tasks>
        </Package>
    </Packages>
</Biml>

Will get you:

Till Next Time