CHECKPOINT: Build Automated Staging Layer

gravatar

Scott Currie

CHECKPOINT: Build Automated Staging Layer

published 08.14.15

last updated 10.05.15


Part of lesson CHECKPOINT: Build Automated Staging Layer.

Share

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.

Finished?

Complete the lesson CHECKPOINT: Build Automated Staging Layer:

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

Comments

gravatar

anthony

8:52am 03.01.16

1-2-CreateTableMetadata.biml

Can you advise on the following error:-

I’ve followed your example step by step:- - The name 'ImportOptions' does not exist in the current context. - I’ve tried a few Stairways and walkthroughs and videos, they all follow a similar pattern in using the ImportOptions.

Thanks.

gravatar

Russel8

12:25pm 04.22.16

I added the following import snippet to get this to work. <#@ import namespace="Varigence.Hadron.CoreLowerer.SchemaManagement" #>

gravatar

Noel8

7:04pm 09.06.16

Instead of <#@ import namespace="Varigence.Hadron.CoreLowerer.SchemaManagement" #> I believe <#@ import namespace="Varigence.Biml.CoreLowerer.SchemaManagement" #> should be used. Note the change from "Hadron" to "Biml."

gravatar

Noel8

8:20pm 09.06.16

I may have done something wrong, but I had to manually create the "stg" schema in order to get things to run.

gravatar

Jay11

10:21pm 10.24.16

So there is a section on "building the biml" but it doesnt tell you how to make multiple builds or steps or anything. It would be helpful if that was expanded as this walkthough does not seem make working code without it.

gravatar

Benjamin

10:17am 11.28.17

I'm getting : Internal Compiler Error: Workflow EmitSsis contains fatal errors. Phase execution halted.For assistance, please send Biml Compiler error messages to support@varigence.com. See other errors for more information. -1 -1

This is when trying to generate the Copy Data package. The error goes away if I change the line to for example: (I changed the TargetColumn name by adding an underscore). Seems like the Biml compiler can't handle the same source and target name. Is there anything I can do to make this work? Thanks. Ben