Import Database Assets into Biml

gravatar

cell

This sample demonstrates importing existing database assets into Biml.

published 09.07.12

last updated 04.29.13


Share

                            


<#@ 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 BIDS Helper:
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 BIDS.  Since it is generating BIML in memory, another
Biml script in Teir 2 will be required to generate a package(s) from the snippet above.

In Mist:
1. Add a Connection to your Mist 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>
                        

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.

You are not authorized to comment. A verification email has been sent to your email address. Please verify your account.

Comments

gravatar

Henk

8:54pm 03.12.13

The above code results in error "The type or namespace name 'ImportResults' could not be found". Using BidsHelper 1.6.2. What needs to be adapted in this code?

gravatar

Paul S. Waters

6:46pm 04.29.13

Henk,

I have added some notes in the snippet to help clarify how it needs to be used.

Thanks, Paul

gravatar

lucazav

7:52am 09.25.13

Henk is right. There is a missing reference for the class 'ImportResults'. Please, follow this thread:

http://varigence.com/Forums?forumName=Biml&threadID=231