Import Database Assets into Biml

A popular application of BimlScript is importing already created database assets into Biml, thereby guaranteeing that your Biml defined assets match your database contents.

The first step is to create a connection to your database. This sample uses a connection named AdventureWorksLT. The second step is to create a C# property that returns all assets from the AdventureWorksLT database. This property is the Results property, which is of type ImportResults. The Results property is defined within a class feature control block, that's delimited by <#+ and #>. A class feature control block lets you define properties, methods, or other code that won't be included in Biml, but can be used by other BimlScript code nuggets. The property's getter locates the AdventureWorksLT connection in the project metadata and calls its ImportDB method. This method is part of the Biml API and performs the work of importing all supported objects from the database, including tables, views, and schemas.

The Results property is used within two expression control blocks. Within the Schemas collection is an expression control block, where the imported schema nodes are converted to Biml, via the GetBiml method. The schema nodes' Biml is then inserted in the Biml snippet. The same thing happens in the Tables collection, where the imported table nodes are converted to Biml and that Biml is inserted in the Biml snippet.

<#@ template language="C#" hostspecific="True"#>
<#@ import namespace="Varigence.Languages.Biml.Connection" #>
<#@ import namespace="Varigence.Hadron.Extensions" #>
<#@ import namespace="Varigence.Hadron.Extensions.SchemaManagement" #>

<#+ public ImportResults Results
  { 
    get 
    {   
      return ((AstOleDbConnectionNode)RootNode.Connections["AdventureWorksLT"]).ImportDB();
    }
  }
#>  

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Schemas>
    <#=Results.SchemaNodes.GetBiml()#>
  </Schemas>
  <Tables>
    <#=Results.TableNodes.GetBiml()#>
  </Tables>
</Biml>

*** Add a connection. For the above snippet to work you will need to do the following:
In BimlExpress:
1. Add a new Biml file to your project.
2. Add the Biml below to the new file.
3. If needed, edit the database and connection string.
4. Select both files before executing the Biml.

Note: The above snippet will not generate a package in SSDT. Since it is generating Biml in memory, another Biml script in Tier 2 will be required to generate a package or packages from the snippet above.

In BimlStudio:
1. Add a Connection to your BimlStudio project or change the name of the connection in the snippet from AdventureWorksLT to one already in your project.
2. Either Execute the snippet to create the biml files in your project or convert it to a Live BimlScript.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <OleDbConnection
      Name="AdventureWorksLT"
      ConnectionString="Data Source=localhost;Initial Catalog=AdventureWorksLT;Provider=SQLNCLI10.1;Integrated Security=SSPI;Connect Timeout=30;"/>
  </Connections>
</Biml>