Skip to main content

Introduction

In this lesson, we will put together everything we have learned so far to create a fully functioning solution to automatically create and load a staging environment for a given source system. This will be entirely code driven with just a few notes for each file.

Code Files​

Be sure to copy each of the below files into BIDSHelper or Mist. Also, you will probably want to use the same file names that I have used. I'll explain why in the "Building the Biml" section below.

1-2-Environment.biml​

This file contains all of your configuration information. It will only change when you want to retarget your solution to run against a different source or target system.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <OleDbConnection Name="Source" ConnectionString="Provider=SQLNCLI11;Server=localhost;Initial Catalog=AdventureWorks2012;Integrated Security=SSPI;" />
        <OleDbConnection Name="Target" ConnectionString="Provider=SQLNCLI11;Server=localhost;Initial Catalog=DataPatternsStaging;Integrated Security=SSPI;" />
    </Connections>
    <Databases>
        <Database Name="DataPatternsStaging" ConnectionName="Target" />
    </Databases>
    <Schemas>
        <Schema Name="stg" DatabaseName="DataPatternsStaging" />
    </Schemas>
</Biml>

1-2-CreateTableMetadata.biml​

This file creates an in-memory version all of the target tables that you will want to create on your staging database. Note that I have added logic to convert all Ansi strings to unicode. If you don't want that, comment it out or delete it. Alternatively, replace it with your own column transformation logic.

<# var sourceConnection = RootNode.DbConnections["Source"]; var importResult = sourceConnection.ImportDB("", "", ImportOptions.ExcludeForeignKey | ImportOptions.ExcludeColumnDefault | ImportOptions.ExcludeViews); #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Tables>
        <# foreach (var table in importResult.TableNodes) { #>
        <Table Name="Staging_<#=table.Schema.Name#>_<#=table.Name#>" SchemaName="DataPatternsStaging.stg">
            <Columns>
                <# foreach (var column in table.Columns) { #>
                <# if (column.DataType == System.Data.DbType.AnsiString) { #>
                <Column Name="<#=column.Name#>" DataType="String" Length="<#=column.Length#>">
                    <Annotations>
                        <Annotation AnnotationType="Tag" Tag="ConvertToUnicode">yes</Annotation>
                    </Annotations>
                </Column>
                <# } else { #>
                <#=column.GetBiml()#>
                <# } #>
                <# } #>
            </Columns>
            <Annotations>
                <Annotation AnnotationType="Tag" Tag="SourceSchemaQualifiedName"><#=table.SchemaQualifiedName#></Annotation>
            </Annotations>
        </Table>
        <# } #>
    </Tables>
</Biml>

1-x-DeployTargetTables.biml​

This file creates a single SSIS package with ExecuteSQL tasks that create each of the staging target tables. Run the resulting package to setup your staging environment.

<#@ template tier="2" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Packages>
        <Package Name="MasterTableDeploy" ConstraintMode="Parallel">
            <Tasks>
                <# foreach (var table in RootNode.Tables) { #>
                <ExecuteSQL Name="Create <#=table.Name#>" ConnectionName="Target">
                    <DirectInput><#=table.GetDropAndCreateDdl()#></DirectInput>
                </ExecuteSQL>
                <# } #>
            </Tasks>
        </Package>
    </Packages>
</Biml>

x-2-CreateLoadPackages.biml​

This file creates a package with dataflow tasks to load each staging target table with data for the corresponding source tables. Note that I have also added data conversion components to perform the ANSI to unicode type conversion for each of the columns that had that option set in the " 1-2-CreateTableMetadata.biml" file. As before, if you want to eliminate or change this logic, please do.

<#@ template tier="2" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Packages>
        <Package Name="Copy Data" ConstraintMode="Parallel">
            <Tasks>
                <# foreach (var table in RootNode.Tables) { #>
                <Dataflow Name="Copy DataPatterns_<#=table.Schema.Name#>_<#=table.Name#>">
                    <Transformations>
                        <OleDbSource Name="Retrieve Rows" ConnectionName="Source">
                            <DirectInput>SELECT <#=table.GetColumnList()#> FROM <#=table.GetTag("SourceSchemaQualifiedName")#></DirectInput>
                        </OleDbSource>
                        <# foreach (var column in table.Columns.Where(c => c.GetTag("ConvertToUnicode") == "yes")) { #>
                        <DataConversion Name="Convert <#=column.Name#>">
                            <Columns>
                                <Column SourceColumn="<#=column.Name#>" TargetColumn="<#=column.Name#>" DataType="String" Length="<#=column.Length#>" />
                            </Columns>
                        </DataConversion>
                        <#} #>
                        <OleDbDestination Name="Load Rows" ConnectionName="Target">
                            <TableOutput TableName="<#=table.ScopedName#>" />
                        </OleDbDestination>
                    </Transformations>
                </Dataflow>
                <# } #>
            </Tasks>
        </Package>
    </Packages>
</Biml>

Building the Biml​

You probably noticed the unusual file naming format used in the previous section. Why do we have those numbers at the beginning of each file name?

As it turns out, you often need to build your Biml solutions in multiple steps. In this case, you first need to build your table creation scripts and then run those scripts so that your database will be available for the building of load packages.

This is where the file numbering scheme helps us. For each step in your build process, you should have either the number of that step or an 'x'. If there is a number, you should include that file when you build that step. If there is an x in that spot, then you don't include the file when building that step.

We can see above that our build has two steps. In step one, we will include the following files:

  • 1-2-Environment.biml
  • 1-2-CreateTableMetadata.biml
  • 1-x-DeployTargetTables.biml

After building this step, we should run the 'MasterTableDeploy' package.

In step two, we will include the following files:

  • 1-2-Environment.biml
  • 1-2-CreateTableMetadata.biml
  • x-2-CreateLoadPackages.biml

Now we can run the 'Copy Data' package to load our staging data!

Conclusion​

Hopefully, you have found this series of Biml lessons useful and informative. As you start down the path of building your own Biml automation solutions, be sure to check back here frequently. We will regularly add new lessons, courses, walkthroughs, videos and other content to help make your journey with Biml even more productive.