In a previous post, we looked at using the XML clause in SQL Server to generate Biml’s XML. The basis of that post was that our package had a pro forma template and we could just substitute code into placeholders. For example, the foundation snippet (below) could just be used and we could replace the … my Type snippet … components with automatically generated code (which was of course derived by the for XML clause in SQL Server).
<Biml xmlns="http://schemas.varigence.com/biml.xsd"> <Connections> <OleDbConnection Name="Source" ConnectionString="Provider=SQLOLEDB;Data Source=localhost\SQL2012;Integrated Security=SSPI;Initial Catalog=AdventureWorksDW2012" /> … my raw file connection snippet … </Connections> <FileFormats> ... my format snippet ... </FileFormats> <Packages> <Package Name="02_Copy_Table_To Raw_File" ConstraintMode="Parallel" ProtectionLevel="DontSaveSensitive" > <Tasks> ... my task snippet ... </Tasks> </Package> </Packages> </Biml>
In that example I pasted the code directly into the placeholder. Notwithstanding the generation of XML (other systems may not include the XML clause or we may wish to refine the query to generate the code), placing the full XML into the snippet placeholders creates a rather large file. This may make it unfriendly and hard to work with and confusing to someone expected to pick up the code.
One method around this is to use the Biml include directive is separate the Biml code into component files. In order to do this we simply place the snippet into a file (the files extension is irrelevant) and then call that file in the Biml code.
For example, if we had a file (F:\temp\biml\connections.txt) that defines our connections, we could simply include the following code into our Biml script.
<#@ include file="F:\temp\biml\connections.txt" #>
Of course, the connections.txt file contains the XML that we would expect to be placed in the … snippet… section. When the Biml is run, the contents of the file are read and are placed where the directive is.