SQL Server BIML 1 min.

// Fully automate the BIML expansion

Fully automate the BIML expansion

With the MIST application from Varigence – this is possible through the command line util that ships with the installation.

When the installation of MIST has finished, you’ll find a new .exe-file in the installation folder called bimlc.exe.

This file is the core engine of the command line util.

According to the online documentation found here – the command line util is pretty straight forward.
The observant reader will notice that the link points to a documentation for hadron.exe – this is the old name for the same tool. See this documentation from Varigence.

The syntax is

`<br />
bimlc.exe -s="<sourcefile>" -t="<targetfolder>"<br />

I’ve made a quick demo biml-project with below code:

`<br />
<# var meta_connectionstring = "Provider=SQLNCLI11;Server=[myServer];Initial Catalog=[myDatabase];Integrated Security=SSPI;"; #><br />
<#<br />
DataTable tables;<br />
tables = ExternalDataAccess.GetDataTable(meta_connectionstring, "[dbo].[storedProcedureThatGivesMeTheData]");<br />
#><br />
<Biml xmlns="http://schemas.varigence.com/biml.xsd"><br />
<Connections><br />
<OleDbConnection Name="BIMLMETA" CreateInProject="true" ConnectionString="Provider=SQLNCLI11;Data Source=[myServer];Integrated Security=SSPI;Initial Catalog=[myDatabase]" /><br />
</Connections><br />
<Packages><br />
<# foreach(DataRow table in tables.Rows) { #><br />
<Package Name="SSIS package <#=table["schema"] #>_<#=table["table"] #>"><br />
</Package><br />
<# } #><br />
</Packages><br />
</Biml><br />
<#@ import namespace="System.Data" #><br />
<#@ import namespace="System.Linq"#><br />

This file is called AutoCompile.biml, and I want to put the generated files in c:\AutoCompilePackages. Then the commandline would be:
<br /> bimlc.exe -s="c:\AutoCompile\AutoCompile.biml" -t="c:\AutoCompilePackages"<br />
When I then press ENTER the compiler starts up and does it’s thing:

BIML autocompile command

The output tells us that there are 9 packages created in each of its own projects.

I can find the compiled projects and packages in my defined output folder.

BIML Autocompile output

And the content of each folder.

BIML autocompile output content

Thats it. Easy job 🙂 thanks to Varigence and their MIST product.