Bulk changes to objects with templates in Biml using Biml-XML

Diesen Artikel finden Sie hier auch in deutscher Sprache.

Maybe you’ve noticed our recent post about Bulk changes to Tasks in BimlExpress. It showed a small example on how you can use the Biml object model and apply common changes or settings to it, like a global DefaultBufferMaxRows setting.

The same approach would also allow to add columns, events, transformation and much more but from a certain point, you may start missing the possibility to do your design using the Biml XML language elements compared to adding them through code.
In BimlStudio, this could be achieved through transformers – a feature not available in BimlExpress. But there are ways to navigate around it which, even though they are by far not as powerful as real transformers, can make your life easier.

To allow us to focus on the template part, we will start by creating some static Biml (a connection, a database, a schema, two tables and two empty packages), just so we’ll have some objects to apply our templates to:

 <Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <OleDbConnection Name="Target" ConnectionString="Provider=SQLNCLI11;Server=localhost;Initial Catalog=TargetDB;Integrated Security=SSPI;"/>
    </Connections>
    <Databases>
        <Database Name="Target" ConnectionName="Target"/>
    </Databases>
    <Schemas>
        <Schema Name="dbo" DatabaseName="Target"/>
    </Schemas>
    <Tables>
        <Table Name="MyTable_1" SchemaName="Target.dbo">
            <Columns>
                <Column Name="Col_1" DataType="String" Length="50" />
                <Column Name="Col_2" DataType="String" Length="50" />
            </Columns>
        </Table>
        <Table Name="MyTable_2" SchemaName="Target.dbo">
            <Columns>
                <Column Name="Col_1" DataType="String" Length="50" />
                <Column Name="Col_2" DataType="String" Length="50" />
            </Columns>
        </Table>
    </Tables>
    <Packages>
        <Package Name="Package_1"/>
        <Package Name="Package_2"/>
    </Packages>
</Biml>

Now let’s say we want to be able to use our templates to add columns to tables and events to packages (BTW: this is the biggest downside of the approach – it won’t work for anything, that you haven’t defined in advance, so you can’t just add a TemplateDataflow and expect it to work for example).
To do so, we will add another Biml file and define a TemplateTable with a column as well as a TemplatePackage with an event:

 <Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Tables>
        <Table Name="TemplateTable" SchemaName="Target.dbo">
            <Columns>
                <Column Name="Load_TimeStamp" DataType="DateTime"/>
            </Columns>
        </Table>
    </Tables>
    <Packages>
        <Package Name="TemplatePackage">
            <Events>
                <Event Name="MyEvent" EventType="OnPostExecute">
                    <Tasks>
                        <ExecuteSQL Name="PostExecute SP" ConnectionName="Target">
                            <DirectInput>
                                <!-- Your Code Here! -->
                            </DirectInput>
                        </ExecuteSQL>
                    </Tasks>
                </Event>
            </Events>
        </Package>
    </Packages>
</Biml> 

Our last step is to bring them together, so we will loop through all our tables that are not the TemplateTable and add all columns from the TemplateTable to each other table.
In the next step, we will loop through all packages that are not our TemplatePackage and add all events from it to all other packages.

If you wanted to add other properties or objects, all you’d need to do would be to add an iteration for that part as well:

 <#@ template language="VB" optionexplicit="False" tier="999"#>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <# for each tbl in RootNode.Tables.Where(Function(c) c.Name <> "TemplateTable") 
		for each col in RootNode.Tables("TemplateTable").Columns 
		 	if tbl.Columns.Where(function(c) c.Name = col.Name).Count = 0 then tbl.Columns.add(col)
		next
	next 
	   
	for each pack in RootNode.Packages.Where(Function(c) c.Name <> "TemplatePackage") 
		for each eve in RootNode.Packages("TemplatePackage").Events 
		 	if pack.events.Where(function(c) c.Name = eve.Name).Count = 0 then pack.Events.add(eve) 
        next
	next #>
</Biml>
 

That’s it – all your packages should now have the PostExecute event from your template – just like all tables should have the timestamp column.

Any questions or comments? We’d love to hear from you at !

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

Happy Biml’ing!