Running a Sample Template

This example shows how a templating engine turns a metadata file and a template into a working SQL statement.

Download the RunDsaAutomation executable from the data solution automation releases page and place it in a working directory with an input folder for the metadata and a templates folder for the template.

The metadata

The metadata describes a single mapping: a source data object, a target (SomeCustomerTable), and two source-to-target column mappings.

{
  "dataObjectMappings": [
    {
      "mappingName": "MyFirstMapping",
      "mappingClassifications": [
        {
          "classification": "Example"
        }
      ],
      "sourceDataObjects": [
        {
          "name": "MyFirstMappingSource"
        }
      ],
      "targetDataObject": {
        "name": "SomeCustomerTable"
      },
      "dataItemMappings": [
        {
          "sourceDataItems": [
            {
              "name": "ColumnOneSource"
            }
          ],
          "targetDataItem": {
            "name": "CustomerId"
          }
        },
        {
          "sourceDataItems": [
            {
              "name": "ColumnTwoSource"
            }
          ],
          "targetDataItem": {
            "name": "CustomerName"
          }
        }
      ]
    }
  ]
}

The template

The Handlebars template loops over the mappings and renders a SELECT statement, aliasing each source column to its target name.

--Example using a templating engine for a simple select statement.
{{#each dataObjectMappings }}

--Working on {{mappingName}}.

SELECT
{{#each dataItemMappings}}
{{sourceDataItems.0.name}} AS {{targetDataItem.name}}{{#unless @last}},{{/unless}}
{{/each}}
FROM [{{targetDataObject.name}}]
WHERE {{dataItemMappings.0.targetDataItem.name}}!=NULL
{{/each}}

Running it

From the directory where the executable resides, run:

.\RunDsaAutomation -i ".\input\MyFirstMapping.json" -p ".\templates\myFirstTemplate.handlebars" -v -o -d .\output -e sql

The flags point the engine at the metadata and template and control the output:

• -i — input metadata (mapping) file
• -p — template to apply
• -o — write the result to a file
• -d — output directory
• -e — output file extension
• -v — verbose logging

The result

The engine combines the metadata with the template and writes a .sql file to the output folder:

--Example using a templating engine for a simple select statement.

--Working on MyFirstMapping.

SELECT
ColumnOneSource AS CustomerId,
ColumnTwoSource AS CustomerName
FROM [SomeCustomerTable]
WHERE CustomerId!=NULL