Sunday, September 23, 2012

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: I brought the Flat File definitions and the scripcompent C# part to external txt files. This also increases the reusability of code.!
<Biml xmlns="">
            File: Add.RowNumber.Include.biml
            Demo on using the Include file
            DB: MS-SQL2012
            BIML: 1.6 VS2010 BIDS Helper
            (c) John Minkjan
        <#@ include file="C:\BIML\FFF\FFF AdventureWorks2012 Person Address.txt" #>
        <#@ include file="C:\BIML\CN\CnOleDBAdventureWorks2012.txt" #>           
        <FlatFileConnection Name ="CnFFAdventureWorks2012PersonAddress"
                            FileFormat ="FFF AdventureWorks2012 Person Address"
        <Package Name="Add.RowNumber.Include" ConstraintMode="Linear">
                <Dataflow Name="DFT Add Rownumber">
                        <OleDbSource Name ="ODS AdventureWorks2012 Person Address"
                                     ConnectionName ="CnOleDBAdventureWorks2012">
                                SELECT [AddressID]
                                FROM [AdventureWorks2012].[Person].[Address]
                        <DerivedColumns Name="DC ADD RowNumber Column">
                                <Column Name="RowNumber" DataType ="Int64">0</Column>
                        <ScriptComponentTransformation Name ="SC Add RowNumber">
                                <#@ include file="C:\BIML\SC\SCP AddRowNumber.txt" #>
                        <FlatFileDestination Name ="FFD AdventureWorks2012 Person Address"
                                             ConnectionName ="CnFFAdventureWorks2012PersonAddress"
                                             Overwrite ="true">
Till Next Time

1 comment:

  1. Can we dynalically pass the Include path as a variable or atleast the file name ?

    <#@ include file="C:\BIML\SC\(Filename)" #>
    <#@ include file="C:\BIML\SC\Pkg[Filename]" #>