Diesen Artikel finden Sie hier auch in deutscher Sprache.

If you’re using Biml, then you have most certainly used the GetDropAndCreateDdl function quite a couple of times. It will smoothly take care of creating your tables in the staging environment including Indexes etc., which makes it equally easy to use and powerful.

Your usual code probably looks more or less like this:

<#@ template tier="2" language="VB" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Packages>
        <Package Name="01_CreateStaging" ConstraintMode="Parallel">
            <Tasks>
                <# for each table as asttablenode in RootNode.Tables  #>
                <ExecuteSQL Name="Create <#=table.Name#>" ConnectionName="Target">
                    <DirectInput><#=table.GetDropAndCreateDdl()#></DirectInput>
                </ExecuteSQL>
                <# next #>
             </Tasks>
        </Package>
    </Packages>
</Biml>

But why would I want to recreate all my tables everytime? This will cause unnecesary IO and could potentially mean, that you will spend hours re-loading the data into those tables – all because of one additional field in one tiny 3 row table.

To avoid recreating exactly what you already have, simply check if there were any changes. This will only require 2 checks:

– Does the table already exist?
– If it exists, is the column definition the same (compared to staging environment and your Biml Metadata)?

If either of those answers is no, let’s re-create the table. As you would expect, this will only require a few lines of extra code:

<#@ template tier="2" language="VB" #>
<# dim desttable as asttablenode
dim desttableresults as ImportResults
dim TableExists as boolean = false
dim targetconnection as AstDbConnectionNode = rootnode.connections("Target") #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Packages>
          <Package Name="01_CreateStaging_Incremental" ConstraintMode="Parallel">
            <Tasks>
                <#	for each table as asttablenode in RootNode.Tables 
					TableExists = false
					DestTableResults= targetConnection.ImportDB(nothing,table.name) 
			   		if DestTableResults.TableNodes.count > 0 then 
			   			desttable = DestTableResults.TableNodes(0)
						if desttable.columns.getbiml = table.columns.getbiml then TableExists = true
					end if
 					if TableExists = false then #>
	                <ExecuteSQL Name="Create <#=table.Name#>" ConnectionName="Target">
	                    <DirectInput><#=table.GetDropAndCreateDdl()#></DirectInput>
	                </ExecuteSQL>
	            <# end if 
				next #>
            </Tasks>
        </Package>
    </Packages>
</Biml>

That’s all it takes, to skip re-creating what you already have. It will save time for you and it will make the DBA happy :)

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!