BIML – An Introduction

Hi,

In this post I would like to introduce an up-coming star in the BI world termed as “BIML” !!!

BIML – Business Intelligence Markup Language and for more information please navigate BIML Wiki

Now to my discovery of the language so far has definitely suggested me some great potential with BIML in the BI world, as it seems an attempt to amalgamate the scripting world with the BI task to automate the manual processes.

To simplify my words above, its a language which is a combination of XML + CSharp (.NET) to define the SSIS packages and even extending to SSAS of microsoft BI and potentially extending to other BI systems adopting similar terminology.

Getting head around !!!

So BIML is nothing but an cleaner version of XML representing your SSIS packages.
(for some who might not be aware, that SSIS packages which appears to be a very freindly UI of drag and drop for DTSX packages, at the backend is nothing but XML)

Not so friendly haii, but its not too bad either if you read little carefully.

Now this native SSIS XML is not so clean in terms of GUID and internal refrences, hence BIML comes at help.

And with the combination of C# scripts (the concept picked from T4 template generation (EntityFramework) and similar to syntax logic of ASP MVC Razor, where C# snippets can define the dynamic HTML) the BIML can be dynamically defined which when expanded can generate some massive SSIS packages which manually would have taken surely a long time to achive.

Now to put words to action, I better illustrate things via demo:

I will generate 3 BIML files in my sample SSIS project which is blank.

And the contents of those 3 BIML files are as follows:

1. Environment.biml

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
	<Connections>
		<OleDbConnection Name="AdventureWorksLT2012" ConnectionString="Provider=SQLNCLI11;Data Source=TROYAN;Integrated Security=SSPI;Initial Catalog=AdventureWorksLT2012"/>
    </Connections>
	<Databases>
		<Database Name="AdventureWorksLT2012" ConnectionName="AdventureWorksLT2012" />
    </Databases>
	<Schemas>
		<Schema Name="SalesLT" DatabaseName="AdventureWorksLT2012"/>
    </Schemas>
</Biml>

2. ImportTables.biml

<#
	var importDb = ((AstOleDbConnectionNode)RootNode.Connections["AdventureWorksLT2012"]).ImportDB("SalesLT","");
	string[] ignoreTables = {"sysdiagrams"};
	String[] ignoreIdx = {"PXML_ProductModel_CatalogDescription"};
	
#>
	
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
	<Tables>
		<# foreach(var table in importDb.TableNodes.Where(t=> !ignoreTables.Contains(t.Name))) {#>
		<Table Name="<#=table.Name #>" SchemaName="AdventureWorksLT2012.SalesLT" LogicalDisplayFolder="AdventureWorksLT2012">
			<Columns>
				<#=table.Columns.GetBiml() #>
            </Columns>
			<Keys>
				<#=table.Keys.GetBiml() #>
            </Keys>
			<Indexes>
				<#=table.Indexes.Where(idx=> !ignoreIdx.Contains(idx.Name)).GetBiml() #>
            </Indexes>
        </Table>
		<# } #>
    </Tables>	
</Biml>

3. GeneratePackage.biml

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
	<Packages>
		<Package Name="GenTables" ConstraintMode="Parallel" LoggingMode="Disabled">
			<Tasks>
				<# foreach(var table in RootNode.Tables){ #>
				<ExecuteSQL Name="Create_<#=table.Name #>" ConnectionName="<#=table.Connection.Name #>" LoggingMode="Enabled" >
					<DirectInput> 
						<#=table.GetTableSql() #>
                    </DirectInput>
				</ExecuteSQL> 
				<# } #>
            </Tasks>
		</Package>
    </Packages>
</Biml>

Ok, code is in place now. But how do I compile/error check if all is okay ???

Now here there is execution concept of BIML which you would need to understand that there is no project build model in existing visual studio so you can just hit the build and it will compile all together and generate the output.

So in order to first compile check for errors, multi-select all the BIML scripts in your project relevant to the required output, as in our case the execution steps of BIML would be as follows:

Environment.biml —> ImportTables.biml —> GeneratePackage.biml

Then to generate the output

And there you have it, you require package with task as defined in BIML for all the relevant table generation and relevant connection information…

Note: For all the above functionality to be achived by BIML within SSIS Visual Studio environment, please install the BIDS Helper

Hope it help …

Downloads