Valid as of release 2.13.0

The following table provides an overview of which Load Process Step (LPS) templates exist in ExMeX Framework Core. Additionally the assigned Load Pattern Id is listed as well as the current LPS template version. Furthermore it is shown which of the global variables are already implemented and compatible in which template (for an overview of global variables see also section Getting started -> Configuration -> How do you set global variables and what is their purpose?).
As a rule, this is the case from LPS template version 4.0.0.

⊗ - global Variable is used by LPS Template
Grey cell - global Variable is not applicable
Empty cell - global Variable is yet not integrated in LPS Template

For the sake of clarity in the table, letters replaced the variable names:

A = SequenceAsDataVaultSK
B = ColumnAuditTrailId
C = ColumnEntityId
D = ColumnRecordSource
E = ColumnLoadTimestamp
F = SatColumnLoadTimestamp
G = SatColumnLoadEndTimestamp
H = ColumnHashDiff
I = ColumnHashDiffCDC
J = ColumnStatusCDC

Section is work in progress!

Templating expressions or ExMeX expressions are the basics of a template. You can use them individually within the plus sign +plus sign+ or combine them and build complex templates in combination with TSQL.

Basic Usage

ExMeX expressions are content enclosed by plus signs + +. In the template below, targetDataObject is a variable enclosed by plus signs, which is called an expression.

'TRUNCATE TABLE [' + targetDataObject + '];'

When the following design metadata is applied to the template,

TargetDataObject = 'ProductMain'

the expressions are interpreted as by the ExMeX compiler as follows:

TRUNCATE TABLE [ProductMain];

Escaping

For the ExMeX compiler the template is a string that starts and ends with an ' (apostrophe). To combine one or more ExMeX expressions e.g. with TSQL to a template the string must be terminated before each expression with a ' (apostrophe) and started again afterwards.

If you want the ExMeX compiler to output a ' (apostrophe), use the “double apostrophe”, '':

'SELECT ''My current Audit Trail Id: '', + @AuditTrailId 
;'

The “template” is interpreted by the ExMeX compiler as follows:

 DECLARE @AuditTrailId BIGINT;
 SELECT 'My current Audit Trail Id: ', + @AuditTrailId
 ;

More information about @AuditTrailId in the next section.

Logging

The ExMeX variable declaration for @AuditTrailId is added automatically by the ExMeX compiler as part of the template. The integer value for @AuditTrailId is set by the ExMeX Logging Framework. Start and end of logging for the module is ensured by the ExMeX compiler.

ExMeX variables

ExMeX expression

ExMeX expressions for the ExMeX Logging Framework

Logging other than start and end of execution (automatically built in by the compiler) is not essential, but helpful for later analysis of statistics about the execution of the module.

It is recommended to save the return values of any DML in local variables before they are cleared by the database:

    SELECT
         @ProcessStatusNumber   = @@ERROR
        ,@RowsInserted          = @@ROWCOUNT
    /*  ,@RowsUpdated           = @@ROWCOUNT */
    /*  ,@RowsDeletedPhysical   = @@ROWCOUNT */
    /*  ,@RowsDeletedLogical    = @@ROWCOUNT */
    ;

By putting the following lines into your template string, the execution mode of the logging stored procedure will be set and the stored procedure itself will be inserted y the compiler. For more details see Framework Core in Depth -> Stored Procedures:

SET @ProcessStepExec = ''I'' /* (S)tart, (U)pdate, (D)elete, (C)lear, (E)nd */;

By adding the ExMeX expression @SqlUspProcessStepVapourTrail to the template 

    '
    /*---------------------------------------------------------------------*/
    /*-- LOGGING: Update Insert information and statistics of VapourTrail  */
    /*---------------------------------------------------------------------*/

    SET @ProcessStepExec = ''I'' /* (S)tart, (U)pdate, (D)elete, (C)lear, (E)nd */;

    ' + @SqlUspProcessStepVapourTrail + '

The ExMeX compiler will interpred it as follows:

    /*---------------------------------------------------------------------*/
    /*-- LOGGING: Update Insert information and statistics of VapourTrail  */
    /*---------------------------------------------------------------------*/

    SET @ProcessStepExec      = 'I' /* (S)tart, (U)pdate, (D)elete, (C)lear, (E)nd */;

    EXEC MetadataZone.uspProcessStepVapourTrail
                     @LoadBatchId           = @LoadBatchId
                    ,@ProcessStepId         = @ProcessStepId
                    ,@ProcessStepExec       = @ProcessStepExec
                    ,@AuditTrailIdIn        = @AuditTrailId
                    ,@AuditTrailIdOut       = @AuditTrailId           OUTPUT
                    ,@SourceEntityClassCode = @SourceEntityClassCode
                    ,@RowsRead              = @RowsRead
                    ,@RowsInserted          = @RowsInserted
                    ,@RowsUpdated           = @RowsUpdated
                    ,@RowsDeletedPhysical   = @RowsDeletedPhysical
                    ,@RowsDeletedLogical    = @RowsDeletedLogical
                    ,@ProcessStatusNumber   = @ProcessStatusNumber
                    ,@ProcessStatusMessage  = @ProcessStatusMessage
                    ,@ErrorSeverity         = @ErrorSeverity
                    ,@ErrorState            = @ErrorState
                    ,@ErrorProcedure        = @ErrorProcedure
                    ,@ErrorLine             = @ErrorLine;