Create and Load a Staging Environment from Scratch in an Hour with Biml

gravatar

Scott Currie

These are the code samples from the PASS BI Virtual Chapter meeting from 2013/12/12. For the video, check out "Create and Load a Staging Environment from Scratch in an Hour with Biml" at http://bi.sqlpass.org/PresentationArchive.aspx

published 12.19.13

last updated 12.19.13


Share

Please visit http://bi.sqlpass.org/PresentationArchive.aspx and find "Create and Load a Staging Environment from Scratch in an Hour with Biml" to download the video that accompanies these code samples. Below are the 4 files, along with their names for use in either BIDSHelper or Mist:

1-2-Environment.biml

<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=DataPatternsStage;Integrated Security=SSPI;" />
  </Connections>
  <Databases>
    <Database Name="DataPatternsStaging" ConnectionName="Target" />
  </Databases>
  <Schemas>
    <Schema Name="stg" DatabaseName="DataPatternsStaging" />
  </Schemas>
</Biml>

1-2-CreateTableMetadata.biml

<#@ import namespace="Varigence.Hadron.CoreLowerer.SchemaManagement"  #>

<#
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

<#@ 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

<#@ 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>
You are not authorized to comment. A verification email has been sent to your email address. Please verify your account.

Comments

gravatar

Phil

11:39pm 02.28.14

Hi Scott, firstly thanks for posting this and the video - I found it very helpful as an intro to BIML generally.

My question is whether your sample would (in principle) work with an Oracle database as the source provider? My initial experiments suggest not - no tasks get written to the schema creation package. I'm using the latest BIDS Helper with VS2012. I'd be grateful for any thoughts you might have.

Thanks, Phil.

gravatar

Scott Currie

2:09am 03.14.14

Yes, people regularly use this sample against Oracle, as well. You can either use one of the ADO.NET or ODBC adapters for Oracle. Alternatively, you can use the Attunity connector, but that works much better in Mist, since you have to use the CustomComponent/CustomSsisConnection syntax in BIDSHelper. Here is a link to a sample of the latter: http://bimlscript.com/Walkthrough/Details/67

gravatar

Kenneth

7:17am 04.07.14

Hi there Scott,

I was wondering, this solution, creates on Conversion task for each coloumn that needs conversion. Could it be done so that all columns ws put into one conversion task?

/best regards Kenneth

gravatar

Scott Currie

6:36pm 04.07.14

To put them all in one data conversion, just substitute: <# foreach (var column in table.Columns.Where(c => c.GetTag("ConvertToUnicode") == "yes")) { #> <# } #>

with this:

<# if (table.Columns.Any(c => c.GetTag("ConvertToUnicode") == "yes")) { #>
<DataConversion Name="Convert ANSI strings to Unicode">
    <Columns>
        <# foreach (var column in table.Columns.Where(c => c.GetTag("ConvertToUnicode") == "yes")) { #>
        <Column SourceColumn="<#=column.Name#>" TargetColumn="<#=column.Name#>" DataType="String" Length="<#=column.Length#>" />
        <# } #>
    </Columns>
</DataConversion>
<# } #>
gravatar

Soren

2:02pm 05.12.14

When I use this script the created SSIS files has a problem with datetime2 columns in the source. When I open the created package, it reports that the metadata for the source is out of synchronization for each datetime2 source column. The external metadata is reported as DBDATETIME2 and the output metadata is set to [DTWSTR] (27).

The ssis packeage MasterTableDeploy has the columns set correctly as datatime2.

Must I treat datetime2 as a special case?

gravatar

Erik

1:05pm 02.20.15

Hi Scott. Amazing! But will this run in Mist 4.0? If not, what will it take to make it do so? Best Regards

gravatar

Christopher6

3:49pm 01.27.17

Hi all,

I would like to know if it is possible to export the content and structure of all the rootnode tables stored in your Biml file to an external flat file. Is there any code available in Biml that would allow to do this?

I would like to use this file to create a metadata table and use this to create a DataVault structure. I am using BimlExpress in Visual Studio 2016.

Any help would be very much appreciated. Thanks in advance!

gravatar

Steven2

9:18pm 03.20.17

Hey, what if we need to do a Delay validation, where can we add that at? Awesome biml code by the way, big thanks :)