How To Start Saving Time

Below is a sample Biml file that demonstrates some possibilities with Biml and BimlScript. Note that this is an illustrative example; it won’t run as-is. The Initial Walkthrough section demonstrates functioning Biml.

The file begins with two code directives that instruct Biml how to process the code nuggets that we'll encounter later in the code sample. While there are many options available, in this example, we simply specify that our code nuggets will be authored in C# (as opposed to Visual Basic .NET). We additionally specify that the System.Data namespace should be imported for use within the code nuggets. This is equivalent to a C# "using" statment or a Visual Basic "Imports" statement.

The core Biml content begins with a common Biml declaration, which is expected in all Biml files. Following the declaration is a Connections element. A connection is one of several root types in the Biml language, and one or more connections can be defined with a connections element. In this Biml file, a single connection has been created. Its Name attribute provides the connection’s name. The ConnectionString attribute specifies the connection string to use for the connection. Connections in Biml can be reused across many packages and other assets. 

    <#@ template language="C#" #>
    <#@ import namespace="System.Data" #>
    <Biml xmlns="http://schemas.varigence.com/biml.xsd">
        <Connections>
            <!-- Creates a connection to the Adventure Works database -->
            <Connection
                Name="AdventureWorks"
                ConnectionString="Provider=SQLNCLI10.1;Data Source=Localhost;Persist Security Info=False;Integrated Security=SSPI;Initial Catalog=AdventureWorksDW" 
                />
        </Connections>
	    <!-- Packages Collection -->
	    <Packages>
		    <!-- A Package -->
		    <Package
		        Name="MyFirstPackage"
		        ConstraintMode="Linear"
			    >
			    <!-- A Package's Tasks -->
			    <Tasks>
                    <ExecuteSQL Name="ExecuteStoredProc" ConnectionName="AdventureWorks">
                        <DirectInput>EXEC usp_StoredProc</DirectInput>
                    </ExecuteSQL>
                    <# foreach (var table in RootNode.Tables) { #>
                        <Dataflow Name="Duplicate <#=table.Name#> Data">
                            <Transformations>
                                <OleDbSource Name="Retrieve Data" ConnectionName="AdventureWorks">
                                    <DirectInput>SELECT * FROM <#=table.Name#></DirectInput>
                                </OleDbSource>
                                <OleDbDestination Name="Insert Data" ConnectionName="AdventureWorks">
                                    <ExternalTableOutput Table="<#=table.Name#>" />
                                </OleDbDestination>
                            </Transformations>
                        </Dataflow>
                    <# } #>
		        </Tasks>
		    </Package>
        </Packages>
    </Biml>

Following the Connections element is a Packages element. A package is another Biml root type that corresponds to an SSIS package (DTSX file). Similar to Connections, Packages can contain one or more Package elements. Each package has a name attribute. The package's linear ConstraintMode instructs the Biml compiler to automatically make the package's tasks run sequentially, whereas a parallel ConstraintMode requires the user to manually specify task ordering via precedence constraints, as you normally would in SSIS (BIDS / SSDT).

A package can have several child collections, one of which is the Tasks collection. The tasks collection is equivalent to the "Control Flow" UI for SSIS (BIDS / SSDT) and contains all child task definitions for the package. This package's tasks collection contains two tasks: an ExecuteSQL task and a Dataflow task surrounded by code nuggets.

Like other Biml elements, the ExecuteSQL task has a Name attribute. Its ConnectionName attribute points to the already defined AdventureWorks connection, indicating where the task's SQL statement will run. The inner DirectInput element contains the actual SQL statement, as straight text. Although not demonstrated here, it's possible to reference the SQL statement from a file connection, external file, or variable.

As was previously noted, the Dataflow task is surrounded by a C# foreach loop, enclosed by special <# and #> tags. These tags mark the beginning and end, respectively, of BimlScript code nuggets. This is your first taste of BimlScript. The foreach statement instructs the Biml compiler to identify each Table in the project metadata. For each table, a Dataflow task is added to the package, where the Dataflow's name includes the table's name. This is accomplished in the Dataflow task's Name attribute, using the <#= and #> tags. The equal sign instructs the Biml compiler to insert the string value of the statement, table.Name in the sample, directly within Biml. Each Dataflow task contains a Transformations collection. This is equivalent to the "Data Flow" UI for SSIS (BIDS/SSDT) and can contain data flow source, transformation, and destination definitions for the task. Like the ExecuteSQL task, the OleDbSource transformation references the AdventureWorks connection and uses a DirectInput element to specify what data will be retrieved. Note that BimlScript can be used within the DirectInput task's text as well.

You may have noticed that the code does not specify data flow input and output path connections. Biml will automatically infer these for you in the majority of cases. In this example, the default output path of the OleDbSource is automatically wired to the input path of the OleDbDestination that follows it. This will happen automatically for all components, based on their defaults and their order in the Biml file, making it quick and easy to define complex dataflows without having to manage repetitive minutia. If you ever need to override these defaults or use a component that lacks default paths (e.g. Multicast or Union All), then Biml also supports the manual specification of data flow input and output path connections.

This example only scratches the surface of Biml's capabilities. As a general rule of thumb, if you can do it in SSIS with BIDS/SSDT, you can do it in Biml. To learn more about other Biml root types, and which attributes can be applied to which Biml elements, consult the Biml Language Reference. To learn more about authoring Biml, continue to the Initial Walkthrough or begin browsing the site.