BIML Schema’s and Tables

If you find a BIML on Tables sample on the internet looking like this one: http://agilebi.com/jwelch/2011/05/26/creating-tables-using-biml-and-bimlscript/ you might run into trouble getting it to work in BIDS 1.6.1.
This is caused because the BIML xsd has undergone some changes. At this moment (September 2012) the good people of Varigence haven’t had the time to update the documentation on their site.
With the help of Scott Currie  of Varigence I got the sample to work.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Annotations>
        <Annotation>
            File: Tables.biml
            Demo biml file on how to create tables
                using BIML from the 1.6.1 BIDS Helper
            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>
    <Databases>
        <Database Name="AdventureWorks2012"
                  ConnectionName="CnOleDBAdventureWorks2012" />
    </Databases>
    <Schemas>
        <Schema Name ="Test" DatabaseName="AdventureWorks2012"
                Owner="dbo"></Schema>
    </Schemas>
    <Tables>
        <Table Name="DimAccount_Test" SchemaName="AdventureWorks2012.Test"
               CompressionType="Page" >
            <!--Attn:    Schema requires DatabaseName.SchemaName
                        If you haven't got a schema defined use
                        DatabaseName.[default]-->
            <Columns>
                <Column Name="AccountKey" />
                <Column Name="ParentAccountKey"
                        IsNullable="true" />
                <Column Name="AccountCodeAlternateKey"
                        IsNullable="true" />
                <Column Name="ParentAccountCodeAlternateKey"
                        IsNullable="true" />
                <!--Attn:    If you don't define a DataType the column will
                            be defined as int  -->
               
                <Column Name="AccountDescription"
                        DataType="String" Length="50"
                        IsNullable="true" />
                <Column Name="AccountType"
                        DataType="String" Length="50" IsNullable="true" />
                <Column Name="Operator"
                        DataType="String" Length="50" IsNullable="true" />
                <Column Name="CustomMembers"
                        DataType="String" Length="300" IsNullable="true" />
                <Column Name="ValueType"
                        DataType="String" Length="50" IsNullable="true" />
                <Column Name="CustomMemberOptions"
                        DataType="String" Length="200" IsNullable="true" />
            </Columns>
        </Table>
    </Tables>
</Biml>

The package create block looks like this:

<#@ template language="C#" hostspecific="True" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Packages>
        <Package Name="Create Tables" AutoCreateConfigurationsType="None" ConstraintMode="Linear">
            <Tasks>
                <# foreach(var table in RootNode.Tables) {#>
                <ExecuteSQL Name="Create <#=table.Name#>" ConnectionName="<#=table.Connection.Name#>">
                    <DirectInput>
                        <#=table.GetTableSql()#>
                    </DirectInput>
                </ExecuteSQL>
                <# } #>                   
            </Tasks>
        </Package>
    </Packages>
</Biml>

Till Next Time