Purpose

This table gives an overview to all used or reserved return codes any stored procedure returns within the TEDAMOH ExMeX Framework.

Motivation

Having a consistent error messaging within the TEDAMOH ExMeX Framework.

Applicability

The overview is a reference book.

Return Codes

TEDAMOH ExMeX Framework version: 2.8.0 or greater.

Purpose

This procedure executes the batches and LoadProcessSteps (LPS) configured in the Data Logistic metadata according to the orchestration.

Motivation

This procedure was developed with the purpose of simplifying the execution of hundreds of LPS and batches. Based on the parameter values passed and the metadata installed in the metadatabase, the procedure orchestrates the data pipeline independently and completely.

Applicability

Usually this procedure is used within the regular Data Pipeline job control.
During operation or testing of the data warehouse solution, the procedure can also be used to restart incorrect or non-executed LPS and to start individual batches or LPS.

Content

The master batch is usually passed to the procedure as a value. The master batch serves as an anchor to execute all batches and LPS located under the master batch.

The master batch itself must be opened before the procedure is called and then it is closed, since this is not done by the procedure for the batch passed to it. The reason for this is that the calling job control executes other processes in addition to the procedure, which are also assigned to the master batch. An example of the described workflow is shown in the TEDAMOH ExMeX Framework in the file ./Manual Batch.sql.

In parallel execution mode: Please make sure SQL Agent Service is running.

M^* = Mandatory parameter

Return Codes

Considerations and consequences

In case of a restart, the procedure reopens and closes the master batch. The reason for this is that in most cases this is a task within the operation of the data warehouse solution and other processes, which are also part of the master batch, are not affected by a restart.

It is recommended to execute the procedure first in trace mode to double check if every paramter value is configured in the correct way and the result is the expected one.

Sample

In this example the master-batch BigOne is used. The following code-snippets shows how the procedure can be executed.

Trace mode examples

EXEC MetadataZone.uspBatchExecuteLoadProcessStep
 @BatchLabel = 'BigOne'
,@BatchExec  = 'T';

EXEC MetadataZone.uspBatchExecuteLoadProcessStep 
  @BatchLabel                   = 'BigOne'
 ,@BatchExec                    = 'T'
 ,@BatchRestartRunVapourTrailId = 10456;

EXEC MetadataZone.uspBatchExecuteLoadProcessStep
 @BatchLabel           = 'BigOne'
 ,@BatchExec           = 'T'
 ,@SingleProcessStepId = 31;

EXEC MetadataZone.uspBatchExecuteLoadProcessStep 
  @BatchLabel                   = 'BigOne'
 ,@BatchExec                    = 'T'
 ,@BatchRestartRunVapourTrailId = 10456
 ,@SingleProcessStepId          = 70;

DECLARE @MultipleBatchLabel MetadataZone.MultipleBatchLabel;

INSERT INTO @MultipleBatchLabel 
  VALUES ('Default - Hub');
INSERT INTO @MultipleBatchLabel 
  VALUES ('Default - Link');

EXEC MetadataZone.uspBatchExecuteLoadProcessStep 
  @BatchLabel         = 'BigOne'
 ,@BatchExec          = 'T'
 ,@MultipleBatchLabel = @MultipleBatchLabel;

DECLARE @MultipleBatchLabel MetadataZone.MultipleBatchLabel;

INSERT INTO @MultipleBatchLabel 
  VALUES ('Default - Hub');
INSERT INTO @MultipleBatchLabel 
  VALUES ('Default - Link');

EXEC MetadataZone.uspBatchExecuteLoadProcessStep 
  @BatchLabel          = 'BigOne'
 ,@BatchExec           = 'T'
 ,@SingleProcessStepId = 59
 ,@MultipleBatchLabel  = @MultipleBatchLabel
;

Execute mode examples

EXEC MetadataZone.uspBatchExecuteLoadProcessStep
 @BatchLabel = 'BigOne'
,@BatchExec  = 'E';

EXEC MetadataZone.uspBatchExecuteLoadProcessStep
 @BatchLabel        = 'BigOne'
,@BatchExec         = 'E'
,@ParallelExecution = 1;

EXEC MetadataZone.uspBatchExecuteLoadProcessStep 
  @BatchLabel                   = 'BigOne'
 ,@BatchExec                    = 'E'
 ,@BatchRestartRunVapourTrailId = 10456;

EXEC MetadataZone.uspBatchExecuteLoadProcessStep
 @BatchLabel           = 'BigOne'
 ,@BatchExec           = 'E'
 ,@SingleProcessStepId = 31;

EXEC MetadataZone.uspBatchExecuteLoadProcessStep 
  @BatchLabel                   = 'BigOne'
 ,@BatchExec                    = 'E'
 ,@BatchRestartRunVapourTrailId = 10456
 ,@SingleProcessStepId          = 70;

DECLARE @MultipleBatchLabel MetadataZone.MultipleBatchLabel;

INSERT INTO @MultipleBatchLabel 
  VALUES ('Default - Hub');
INSERT INTO @MultipleBatchLabel 
  VALUES ('Default - Link');

EXEC MetadataZone.uspBatchExecuteLoadProcessStep 
  @BatchLabel         = 'BigOne'
 ,@BatchExec          = 'E'
 ,@MultipleBatchLabel = @MultipleBatchLabel;

DECLARE @MultipleBatchLabel MetadataZone.MultipleBatchLabel;

INSERT INTO @MultipleBatchLabel 
  VALUES ('Default - Hub');
INSERT INTO @MultipleBatchLabel 
  VALUES ('Default - Link');

EXEC MetadataZone.uspBatchExecuteLoadProcessStep 
  @BatchLabel          = 'BigOne'
 ,@BatchExec           = 'E'
 ,@SingleProcessStepId = 59
 ,@MultipleBatchLabel  = @MultipleBatchLabel
;

Valid as of release 2.12.0

Purpose

The purpose of this stored procedure is to change global variables which configures and controls different parts of the ExMeX Framework Core.

Motivation

The existing configuration in scripts should be made more variable and be usable by all parts of the ExMeX Framework Core at runtime. This is only possible if more and more settings are made directly in the database in the future. Therefore a stored procedure is needed for a better maintenance and access by companion app.

Applicability

Usually this procedure is used for the first configuration or within the DevOps maintenance operations to change global variables for a different behaviour of the ExMeX Framework Core.

See also section Getting Started -> Configuration.

Execution

See also examples below.

EXEC MetadataZoneCore.uspConfigExMeXFramework
     @VariableName         = <Variable Name to change>    
    ,@VariableValue        = <Value to set of Variable Name>    
    ,@VariableDatatype     = <Datatype of Variable Name - Do not change> 
    ,@VariableDescription  = <Description of Variable Name>

Which parameter provides the usp?

The following table lists all parameter which are available in the stored procedure. If no default value is specified the parameter is mandatory.

M^* = Mandatory parameter

Return Codes

This stored procedure returns the values listed in the following table.

Considerations and consequences

The stored procedure updates the following tables in the Metadata Zone Core:

• MetadataZoneCore.HubConfigExMeXFramework and
• MetadataZoneCore.SatConfigExMeXFramework

These tables should not be changed manually. A reading access takes place for all parts of the ExMeX Framework Core in the Metadata Zone via the view MetadataZone.ConfigExMeXFramework.

Since a change of the global variables usually has far-reaching consequences for all parts of the ExMeX Framework Core, they should be chosen carefully.

Example

The following code snippets show how the values can be changed using the global variable UseTargetEntityDB as an example.

Activate UseTargetEntityDB

EXEC MetadataZoneCore.uspConfigExMeXFramework
     @VariableName         = 'UseTargetEntityDB'     
    ,@VariableValue        = '1'    
    ,@VariableDatatype     = 'BIT'
    ,@VariableDescription  = 'Switch functionality to enable or  usage of SQL-Statement USE [<TargetDatabaseName>] within all LPS. 0 = Off, 1 = On'

Disable UseTargetEntityDB

    EXEC MetadataZoneCore.uspConfigExMeXFramework
     @VariableName         = 'UseTargetEntityDB'     
    ,@VariableValue        = '0'    
    ,@VariableDatatype     = 'BIT'
    ,@VariableDescription  = 'Switch functionality to enable or  usage of SQL-Statement USE [<TargetDatabaseName>] within all LPS. 0 = Off, 1 = On'    

TEDAMOH ExMeX Framework version: 2.10.0 or greater.

Purpose

This procedure maintains filter on source objects for LoadProcessSteps (LPS) configured in the Data Logistic metadata.

Motivation

This procedure was developed with the purpose of simplifying the maintenance of filter conditions on source objects attributes for LPS. Based on the parameter values passed and the metadata installed in the metadatabase, the procedure stores filter conditions for one to many attribute on each source objects.

The filter conditions can also be used for housekeeping/archiving data.

Applicability

Usually this procedure is used within the regular Data Pipeline job control.
During operation or testing of the data warehouse solution, the procedure can also be used to reduce the amount of data which is populated by each individual batch or LPS.

Content

By setting filters on one or more possible columns on a source object for each LPS it is possible to reduce the amount of data to be processed. This feature is mainly introduced to filter from incoming interface to stage layer (volatile zone), but implemented to use it in any LPS.

Which filter conditions are available?

Which parameter provides the usp?

M^* = Mandatory parameter

Overview which parameters are mandatory depending on the coosen filter condition

X   = Mandatory parameter
(X) = Optional parameter

Applicable filter format elements for parameter @EntityClassAttributeFilterFormat

Filter format examples

Return Codes

Considerations and consequences

Sample

In this section different examples are shown. To keep examples simple first are some variables are declared. Last the usp will be executed. These two parts are needed all the time. Copy the one middle section (examples) as needed.

First -> Middle (choose one) -> Last

How to get the filter out of the DAPI please go to the last example.

The following code-snippets shows how the procedure can be executed.

First: Declare variables for examples

DECLARE @ReturnCode                                     INT             
       ,@ModelCode			                VARCHAR(100)    
       ,@EntityClassCode                                VARCHAR(100)    
       ,@AttributeCode                                  VARCHAR(100)    
       ,@EntityClassAttributeFilterConditionPos         TINYINT         
       ,@EntityClassAttributeFilterCondition            VARCHAR(10)     
       ,@EntityClassAttributeFilterInnerClusterType     VARCHAR(10)     
       ,@EntityClassAttributeFilterOuterClusterType     VARCHAR(10)     
       ,@EntityClassAttributeFilterClusterPos           TINYINT        
       ,@EntityClassAttributeFilterClusterLevel         TINYINT         
       ,@EntityClassAttributeFilterUnit                 VARCHAR(32)     
       ,@EntityClassAttributeFilterValue                VARCHAR(1024)   
       ,@EntityClassAttributeFilterDatatype             VARCHAR(128)    
       ,@EntityClassAttributeFilterFormat               VARCHAR(128)    
       ,@IsHousekeeping                                 BIT
       ,@IsNOT                                          BIT
       ,@Action                                         CHAR(1)
       ,@StateTimeFrom                                  DATETIME2       
       ,@StateTimeBefore                                DATETIME2       
       ,@EntityClassFilterCondition                     VARCHAR(2048)   
;

Middle section: Set filter LIKE on column.

    SET @ModelCode		                    = 'STAGE_LAYER'
    SET @EntityClassCode                            = 'VehicleLicensePlate'
    SET @AttributeCode                              = 'LicensePlate'
    SET @EntityClassAttributeFilterConditionPos     = 1
    SET @EntityClassAttributeFilterCondition        = 'LIKE'
    SET @EntityClassAttributeFilterValue            = '''KS%'''    

Middle section: Set second filter LIKE on same column.

    SET @ModelCode		                    = 'STAGE_LAYER'
    SET @EntityClassCode                            = 'VehicleLicensePlate'
    SET @AttributeCode                              = 'LicensePlate'
    SET @EntityClassAttributeFilterConditionPos     = 2
    SET @EntityClassAttributeFilterCondition        = 'LIKE'
    SET @EntityClassAttributeFilterValue            = '''XY%'''        

Middle section: Delete all filter on same column.

    SET @ModelCode	                            = 'STAGE_LAYER'
    SET @EntityClassCode                            = 'VehicleLicensePlate'
    SET @AttributeCode                              = 'LicensePlate'
    SET @Action                                     = 'd'    

Middle section: Insert filter LAST column with Oracle date conversion and date format.

    SET @ModelCode			            = 'StageLayerHiZ'
    SET @EntityClassCode                            = 'DataWithFancyFullHiZ'
    SET @AttributeCode                              = 'AuditTrailId'
    SET @EntityClassAttributeFilterCondition        = 'LAST'
    SET @EntityClassAttributeFilterUnit             = 'WEEK'
    SET @EntityClassAttributeFilterValue            = '20'
    SET @EntityClassAttributeFilterDatatype         = 'ORACLE_DATE'
    SET @EntityClassAttributeFilterFormat           = 'YYYYMMDD'

Middle section: Insert (second, accidentally) filter LAST column with Oracle date conversion and date format.

    SET @ModelCode		                    = 'StageLayerHiZ'
    SET @EntityClassCode                            = 'DataWithFancyFullHiZ'
    SET @AttributeCode                              = 'AuditTrailId'
    SET @EntityClassAttributeFilterConditionPos     = 2
    SET @EntityClassAttributeFilterCondition        = 'LAST'
    SET @EntityClassAttributeFilterUnit             = 'YEAR'
    SET @EntityClassAttributeFilterValue            = '2'
    SET @EntityClassAttributeFilterDatatype         = 'ORACLE_DATE'
    SET @EntityClassAttributeFilterFormat           = 'YYYY'

Middle section: Delete single filter (accidentally inserted before) on specific column position.

    SET @ModelCode		                    = 'StageLayerHiZ'
    SET @EntityClassCode                            = 'DataWithFancyFullHiZ'
    SET @AttributeCode                              = 'AuditTrailId'
    SET @EntityClassAttributeFilterConditionPos     = 2
    SET @Action                                     = 'D'

Last: Execute usp to set filter in metadata database

EXEC @ReturnCode = MetadataZone.uspEntityClassAttributeFilter
                 @ModelCode		                         = @ModelCode				             
                ,@EntityClassCode                                = @EntityClassCode                       
                ,@AttributeCode                                  = @AttributeCode                     
                ,@EntityClassAttributeFilterConditionPos         = @EntityClassAttributeFilterConditionPos
                ,@EntityClassAttributeFilterCondition            = @EntityClassAttributeFilterCondition   
                ,@EntityClassAttributeFilterInnerClusterType     = @EntityClassAttributeFilterInnerClusterType 
                ,@EntityClassAttributeFilterOuterClusterType     = @EntityClassAttributeFilterOuterClusterType 
                ,@EntityClassAttributeFilterClusterPos           = @EntityClassAttributeFilterClusterPos   
                ,@EntityClassAttributeFilterClusterLevel         = @EntityClassAttributeFilterClusterLevel
                ,@EntityClassAttributeFilterUnit                 = @EntityClassAttributeFilterUnit        
                ,@EntityClassAttributeFilterValue                = @EntityClassAttributeFilterValue       
                ,@EntityClassAttributeFilterDatatype             = @EntityClassAttributeFilterDatatype  
                ,@EntityClassAttributeFilterFormat               = @EntityClassAttributeFilterFormat
                ,@IsHousekeeping                                 = @IsHousekeeping
                ,@IsNOT                                          = @IsNOT
                ,@Action                                         = @Action
                ,@StateTimeFrom                                  = @StateTimeFrom                         
                ,@StateTimeBefore                                = @StateTimeBefore                       
                ,@EntityClassFilterCondition                     = @EntityClassFilterCondition OUTPUT           
;
SELECT @ReturnCode, @EntityClassFilterCondition
;

DAPI: Select all filters on DAPI for an Entity

Each filter condition on a column must be part in the overall filter when data is extracted. In SQL this will be the WHERE-CLAUSE. All filter conditions are connected the the logical operator which is provided in column SourceFilterOuterCluster ('AND' or 'OR').

SELECT  SourceEntityClassCode
       ,SourceAttributeCode  
       ,SourceAttributeFilterCondition
       ,SourceAttributeFilterOuterClusterType
       ,IsTargetHistory
       ,TargetEntityClassCode
       ,TargetAttributeCode  
       ,HousekeepingAmount
       ,HousekeepingUnit
FROM MetadataZone.MetadataForGenerateSource2StageLayer
WHERE (SourceModelCode       = @ModelCode				          
   AND SourceEntityClassCode = @EntityClassCode)
  OR  (TargetEntityClassCode = @EntityClassCode)
;

Purpose

The purpose of this stored procedure is to log data logistic statistics of Load Process Steps (LPS) executed within the ExMeX Framework Core or from outside trough 3rd party products, e.g. SSIS or Python scripts, to the ExMeX Logging Framework.

Motivation

The motivation for this stored procedure is a consistent logging of LPS’s in the corresponding Load Process Step Vapour Trail table. But also the reduction of errors while logging, e.g. wrong insert or updates, during development of LPS templates as well as the simplified use of logging purposes by third parties.

Applicability

This stored procedure is the link to the ExMeX logging framework. It is used by the ExMeX Framework Core, and by third party protducts to log the execution (start, end, insert, updates, etc.) of LPS’s.

A precondition for executing this stored procedure is that a batch —to which the LPS is associated— has already been started/opened in the ExMeX Logging Framework. See also the documentation of the stored procedure uspBatchVapourTrail in this section.

Content

This stored procedure is used by any LPS to ensure consistent logging into the ExMeX logging framework.

The stored procedure can be executed in different modes. Depending on this modes, the stored procedure logs the passed parameters differently in the Load Process Step Vapour Trail table:

All timestamps in the Load Process Step Vapour Trail table are stored in Coordinated Universal Time (UTC).

Which ProcessStatusNumber are available?

The following table lists all states of ProcessStatusNumber:

Execution 

See also code snippets in the example section below.

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;

Which parameter provides the usp?

The following table lists all parameter which are available for the stored procedure. If no default value is specified the parameter is mandatory:

M^* = Mandatory parameter

Return Codes

This stored procedure returns the values listed in the following table:

Considerations and consequences

The passing of parameters should be done very carefully, as the quality of logging depends on it. For example, the calculation of the number of lines must be taken into account for the Insert and Delete modes. Or submitted status numbers or messages may have effects.

The stored procedure inserts into, updates or deletes from the following tables:

• MetadataZone.ProcessStepVapourTrail
• MetadataZone.SatProcessStepDeltaExportOverride
• MetadataZone.SatProcessStepDeltaExport
• MetadataZoneStage.ProcessStepDeltaExportOverride

Example

The following code snippets show how the values can be changed.

-- Provided by your script or by generated LPS
SET @EntityId	           = <YourEntityId>
SET @LoadBatchId	   = <YourLoadBatchId>
SET @ProcessStepId         = <YourProcessStepId>
SET @SourceEntityClassCode = <YourSourceEntityClassCode (aka table code)>

-- Set execution mode for stored procedure
SET @ProcessStepExec       = 'S' 

-- Execute stored procedure
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;
						

-- Provided by your script or by generated LPS
SET @EntityId	           = <YourEntityId>
SET @LoadBatchId           = <YourLoadBatchId>
SET @ProcessStepId         = <YourProcessStepId>
SET @SourceEntityClassCode = <YourSourceEntityClassCode (aka table code)>

-- DML
<Your Update DML>

-- Get return values of DML
SELECT
      @ProcessStatusNumber	= @@ERROR
     ,@RowsUpdated		= @@ROWCOUNT

-- Set execution mode for stored procedure
SET @ProcessStepExec       = 'U' 

-- Execute stored procedure
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;
						

-- Provided by your script or by generated LPS
SET @EntityId              = <YourEntityId>
SET @LoadBatchId           = <YourLoadBatchId>
SET @ProcessStepId         = <YourProcessStepId>
SET @SourceEntityClassCode = <YourSourceEntityClassCode (aka table code)>

-- DML
<Your Insert DML>

-- Get return values of DML
SELECT
      @ProcessStatusNumber	= @@ERROR
     ,@RowsInserted		= @@ROWCOUNT

-- Set execution mode for stored procedure
SET @ProcessStepExec       = 'I' 

-- Execute stored procedure
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;
						

-- Provided by your script or by generated LPS
SET @EntityId	           = <YourEntityId>
SET @LoadBatchId           = <YourLoadBatchId>
SET @ProcessStepId         = <YourProcessStepId>
SET @SourceEntityClassCode = <YourSourceEntityClassCode (aka table code)>

-- DML
<Your Insert DML>

/*---------------------------------------------------------------------*/
/*-- Get error information of DML after fail.                          */
/*---------------------------------------------------------------------*/
SELECT @ProcessStatusNumber  = ERROR_NUMBER()
      ,@ProcessStatusMessage = ERROR_MESSAGE()
      ,@ErrorSeverity        = ERROR_SEVERITY()
      ,@ErrorState           = ERROR_STATE()
      ,@ErrorProcedure       = ERROR_PROCEDURE()
      ,@ErrorLine            = ERROR_LINE()
;

-- Set execution mode for stored procedure
SET @ProcessStepExec       = 'E' 

-- Execute stored procedure
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;
						

Valid as of release 2.13.0

Purpose

The purpose of this stored procedure is to change batch specific settings which configures and controls execution of batches within the ExMeX Framework Core.

Motivation

The batch-specific settings were previously only possible via the ExMeX Companion App. And then only as part of the main configuration of a batch. This led to some disadvantages in the general configuration and in deployment.
The stored procedure decouples the specific settings from the general batch configuration. Thus, a separate deployment as well as an environment-specific configuration of the specific batch settings is possible.

Applicability

The stored procedure is mainly used by the ExMeX Companion App (valid as of App version 0.4.0). However, it can also be used independently, in an additional tool.
Since the batch-specific settings depend on the respective environment, the stored procedure is not part of the ExMeX Framework Core metadata installation.
In principle, it would be possible to integrate specific scripts created for each environment via the usr area.

Execution

The recommended way to configure the batch specific settings is with the ExMeX Companion App. However, it can be useful to execute the stored procedure directly, e.g. in a script:

EXEC MetadataZoneCore.uspSetBatchSetting
     @BatchLabel             = <Batch Label to change>
    ,@IsActive               = <0 = Off, 1 = Active>
    ,@ExitOnError            = <0 = Off, 1 = Exit>
    ,@MaxSqlCommandInChunk   = <Set max number of LPS>	

(See also examples below.)

There is also the 'Set Batch Setting' tool that supports and simplifies the development of a script.

Go to Documentation

Which parameter provides the usp?

The following table lists all parameter which are available in the stored procedure. If no default value is specified the parameter is mandatory.

M^* = Mandatory parameter

Return Codes

This stored procedure returns the values listed in the following table.

Considerations and consequences

The stored procedure updates the following table in the Metadata Zone Core:

• MetadataZoneCore.SatLoadBatchSetting

These tables should not be changed manually. A reading access takes place for all parts of the ExMeX Framework Core in the Metadata Zone via the view MetadataZone.LoadBatchNowNow.

Since a change of the global variables usually has far-reaching consequences for all parts of the ExMeX Framework Core, they should be chosen carefully.

Example

The following code snippets show how the values can be changed using the global variable UseTargetEntityDB as an example.

Disable Batch, no other batch-specific settings

EXEC MetadataZoneCore.uspSetBatchSetting
     @BatchLabel             = N'BigOne'
    ,@IsActive               = 0
    ,@ExitOnError            = NULL
    ,@MaxSqlCommandInChunk   = NULL

Set MaxSqlCommandInChunk to 20, no other batch-specific settings

EXEC MetadataZoneCore.uspSetBatchSetting
     @BatchLabel             = N'BigOne'
    ,@IsActive               = NULL
    ,@ExitOnError            = NULL
    ,@MaxSqlCommandInChunk   = 20

Purpose

The purpose of this stored procedure is to override the value in column DeltaExportAssertionTimeFrom in table MetadataForExecuteDataLogisticLoadProcessStep for a Load Process Step (LPS) at execution time in data logistics.

Motivation

The motivation for this stored procedure has different use cases but not limited to these:

• Set a timestamp to perform a LPS initial load from a source to one or many target object(s).
• Set a timestamp to perform a LPS reload of a defined time period from a source to one or many target object(s) in the case of errors or other issues.
• Set a different timestamp for a LPS to ensure an extract of a slightly different time period from a source which is not reliable.

Applicability

This stored procedure is applied every time the timestamp value in table MetadataForExecuteDataLogisticLoadProcessStep, column DeltaExportAssertionTimeFrom, needs to be replaced with a different timestamp, for a Load Process Step (LPS), that is provided by the ExMeX Framework Core.

Content

This stored procedure is used to override the value in column DeltaExportAssertionTimeFrom in table MetadataForExecuteDataLogisticLoadProcessStep provided by the ExMeX framework core.

A batch, which contains all LPS to override the DeltaExportAssertionTimeFrom timestamp can be submitted to the procedure as a value.

A single LPS within a batch, to override the DeltaExportAssertionTimeFrom timestamp can be submitted to the procedure as values.

The LPS’s, which received a new timestamp by this stored procedure, can use the newly set value in column DeltaExportAssertionTimeFrom during execution. This information is provided by the DAPI MetadataForExecuteDataLogisticLoadProcessStep.

Execution

See also code snippets in the example section below.

EXEC MetadataZone.uspSetDeltaExportTimePeriod 
  @BatchLabel                   = <Your Batch Label>
 ,@LoadProcessStepId            = <Your (Load Process) Step Id>
 ,@DeltaExportAssertionTimeFrom = <Your timestamp>
 ;

Which parameter provides the usp?

The following table lists all parameter which are available for the stored procedure. If no default value is specified the parameter is mandatory:

M^* = Mandatory parameter

Return Codes

This stored procedure returns the values listed in the following table:

Considerations and consequences

The stored procedure inserts data into the table ProcessStepDeltaExportOverride. This data is used within the ExMeX Framework Core by the stored procedure uspProcessStepVapourTrail.

Override a timestamp of a batch, in column DeltaExportAssertionTimeFrom in table MetadataForExecuteDataLogisticBatch is not possible.

Example

The following code snippets show how the values can be changed.

-- Execute stored procedure
EXEC MetadataZone.uspSetDeltaExportTimePeriod 
  @BatchLabel                   = 'Example Hub (Core)'
 ,@LoadProcessStepId            = 1256
 ,@DeltaExportAssertionTimeFrom = '2020-01-01 10:04:46.5187419'
 ;

-- Execute stored procedure
EXEC MetadataZone.uspSetDeltaExportTimePeriod 
  @BatchLabel                   = 'Example Hub (Core)'
 ,@LoadProcessStepId            = NULL
 ,@DeltaExportAssertionTimeFrom = '2020-01-01 10:04:46.5187419'
 ;

Valid as of release 3.0.0

Purpose

The purpose of this stored procedure is to allow any ExMeX Framework Core administrator to add or insert a section into a module-template (Load Process Step (LPS)) or to overwrite a given section of any provided module by the ExMeX Framework Core. This allows a user-specific creation of templates for modules.

Furthermore, this stored procedure creates the possibility to provide additional templates for modules in other code languages or for other target platforms.

Motivation

The motivation for this stored procedure is driven by different use cases but not limited to these:

• Adding a table partition switch to switch the current partition —in the target object— within the module before loading from a source object to a target object — applies at Load Pattern Id level
• Add custom SQL code to design a module for PIT or Bridge target objects according to your own requirements — applies at Mapping Object level
  Overwrite an ExMeX Framework Core Template, e.g. the Hub DML (insert statement) in the batch 'Initital Hub Load', with a special version for an initial loading of the target object — applies at Load Pattern Id level
• Add new template languages, e.g. for Oracle or Azure

Applicability

The stored procedure is applied each time a template code snipped (section) for a module (LPS) template is provided to the ExMeX Framework Core. The section needs to be created, added, updated or deleted by an administrator. This usually happens during the metadata installation. But not limited to.
If a section is changed directly at the database we recommend to download/extract the installation script for the code snippet and store it in your code versioning.

Content

This stored procedure is used to modify or customize code snippets within the module template tables provided by the ExMeX Framework Core.

To the stored procedure can be passed a code snippet on different valid levels:

• General, applies to all the following
• For a specific Load Pattern Id
• For a batch
• For a mapping object
• Or any combination of load pattern id, or mapping object

The new code snippets are applied immediately during subsequent compilation of the LPS.

Execution

See also code snippets in the example section below.

EXEC MetadataZoneCore.uspSetCompilerSqlExtendLps 
     @TemplateLanguageCode      <>
    ,@TemplateClassification    <>
    ,@TemplateComment           <>
    ,@TemplateVersion           <>
    ,@TemplateDate              <>
    ,@TemplateCodeSnippet       <>

 -- Action and return code 
    ,@ProcessStepType           < (optional)>
    ,@Action                    < 'R', 'I', 'U', 'D', 'S' or 'DL'>

-- Type of load pattern (e.g. Hub, Sat, PIT)
    ,@LoadPatternId             < (optional)>

-- Mapping/LPS (optional)
    ,@SourceModelCode           < (optional)>
    ,@SourceEntityClassCode     < (optional)>
    ,@TargetModelCode           < (optional)>
    ,@TargetEntityClassCode     < (optional)>
    ,@MappingRole               < (optional)>
;

Which parameter provides the usp?

The following blocks list all parameters available for the stored procedure:

N'TSQL'

N'<#=preBodySection#>'

N'ExMeX User Template - 
Override/Extension for 
CDCodlb template in preBodySection'

N'1.0.0'

N'20220324'

N'
[…]
/* Partition Switch */
DECLARE @CreatePartition NVARCHAR(MAX);
[…]
'

SELECT
[…]
WHERE textCol IN ('I', 'U', 'D')

SELECT
[…]
WHERE textCol IN (''I'', ''U'', ''D'')

SELECT SourceModelCode
 ,SourceEntityClassCode
 ,TargetModelCode
 ,TargetEntityClassCode
 ,MappingRole
FROM MetadataZone.Mapping

EXEC [...]
    ,@SourceModelCode        = N'STAGE_LAYER'
    ,@TargetModelCode        = N'CoreLayerwithHashKey'
    ,@ProcessStepType        = N'DEF'
    ,@SourceEntityClassCode  = N'ArtworkInMuseum'
    ,@TargetEntityClassCode  = N'HubMuseum'
    ,@MappingRole            = N'ArtworkInMuseum2HubMuseum'
    ,@ProcessStepType        = 'DEF'
[...]

Return Codes

This stored procedure returns the values listed below:

Considerations and consequences

With this stored procedure it is possible to extend the templates of the ExMeX Framework Core or to overwrite parts of the supplied templates.

Templates changed or extended in this way are used by the ExMeX compiler (usp Compiler) during the next installation of the metadata to compile the modules (LPS).

A change of the templates, independently of whether a template was extended or overwritten, has far-reaching consequences. This should always be thought of. A thoughtless change can mean severe consequences.

Example

The following code snippets show how the values can be changed. The examples, if used as shown, will break the hub modules. They are not longer executable. If you follow the examples you should at least execute the last example (delete) to avoid severe consequences!

DECLARE  @TemplateLanguageCode      NVARCHAR(256)   = NULL
        ,@TemplateClassification    NVARCHAR(256)   = NULL
        ,@TemplateComment           NVARCHAR(1024)  = NULL
        ,@TemplateVersion           NVARCHAR(20)    = NULL
        ,@TemplateDate              DATE            = NULL
        ,@TemplateCodeSnippet       NVARCHAR(MAX)   = NULL
        -- Type of load pattern (e.g. Hub, Sat, PIT)
        ,@LoadPatternId             BIGINT          = NULL
	 -- Action and return code 
        ,@Action                    CHAR(2)         = NULL
        ,@ReturnCode                INT             = NULL
        -- Mapping/LPS (optional)
        ,@SourceModelCode           NVARCHAR(100)   = NULL
        ,@SourceEntityClassCode     NVARCHAR(100)   = NULL
        ,@TargetModelCode           NVARCHAR(100)   = NULL
        ,@TargetEntityClassCode     NVARCHAR(100)   = NULL
        ,@MappingRole               NVARCHAR(256)   = NULL
        ,@ProcessStepType           NVARCHAR(50)    = NULL
;


SET @TemplateLanguageCode   = N'TSQL'

-- Use 'S' to get all available template sections
--
-- Set type of action 'I', 'U', 'R', 'S' or 'D'
SET @Action                 = 'S'


EXEC @ReturnCode = MetadataZoneCore.uspSetCompilerModuleTemplate
     @TemplateLanguageCode   = @TemplateLanguageCode  
    ,@TemplateClassification = @TemplateClassification
    ,@TemplateComment        = @TemplateComment       
    ,@TemplateVersion        = @TemplateVersion       
    ,@TemplateDate           = @TemplateDate          
    ,@TemplateCodeSnippet    = @TemplateCodeSnippet   
    ,@LoadPatternId          = @LoadPatternId         
    ,@SourceModelCode        = @SourceModelCode       
    ,@SourceEntityClassCode  = @SourceEntityClassCode 
    ,@TargetModelCode        = @TargetModelCode       
    ,@TargetEntityClassCode  = @TargetEntityClassCode 
    ,@MappingRole            = @MappingRole           
    ,@ProcessStepType        = @ProcessStepType       
    ,@Action                 = @Action                
;

SELECT 'ReturnCode: ',@ReturnCode;

DECLARE  @TemplateLanguageCode      NVARCHAR(256)   = NULL
        ,@TemplateClassification    NVARCHAR(256)   = NULL
        ,@TemplateComment           NVARCHAR(1024)  = NULL
        ,@TemplateVersion           NVARCHAR(20)    = NULL
        ,@TemplateDate              DATE            = NULL
        ,@TemplateCodeSnippet       NVARCHAR(MAX)   = NULL
        -- Type of load pattern (e.g. Hub, Sat, PIT)
        ,@LoadPatternId             BIGINT          = NULL
	 -- Action and return code 
        ,@Action                    CHAR(2)         = NULL
        ,@ReturnCode                INT             = NULL
        -- Mapping/LPS (optional)
        ,@SourceModelCode           NVARCHAR(100)   = NULL
        ,@SourceEntityClassCode     NVARCHAR(100)   = NULL
        ,@TargetModelCode           NVARCHAR(100)   = NULL
        ,@TargetEntityClassCode     NVARCHAR(100)   = NULL
        ,@MappingRole               NVARCHAR(256)   = NULL
        ,@ProcessStepType           NVARCHAR(50)    = NULL
;


SET @TemplateLanguageCode   = N'TSQL'

-- Set main body section 
SET @TemplateClassification = N'<#=mainBodySection#>'
SET @TemplateComment        = N'ExMeX User Template - Example in documentation'
SET @TemplateVersion        = N'1.0.0'
SET @TemplateDate           = N'20220301'

-- Use 'I' to insert/override code snippet
--
-- Set type of action 'I', 'U', 'R', 'S' or 'D'
SET @Action                 = 'I'

-- Set load pattern to 1 (Hub)
--
-- Type of load pattern (e.g. Hub, Sat, PIT)
SET @LoadPatternId          = 1

-- Code snippet for template
SET @TemplateCodeSnippet    = N'
-- ExMeX User Template - Example in documentation
INSERT INTO ['' + TargetDatabaseName + ''].['' + TargetSchemaName + ''].['' + TargetEntityClassCode + '']
 SELECT 
    ''''all with my much more better DML statement''''
 FROM IKnowItBetterTable
 ;
'
;

EXEC @ReturnCode = MetadataZoneCore.uspSetCompilerModuleTemplate
     @TemplateLanguageCode   = @TemplateLanguageCode  
    ,@TemplateClassification = @TemplateClassification
    ,@TemplateComment        = @TemplateComment       
    ,@TemplateVersion        = @TemplateVersion       
    ,@TemplateDate           = @TemplateDate          
    ,@TemplateCodeSnippet    = @TemplateCodeSnippet   
    ,@LoadPatternId          = @LoadPatternId         
    ,@SourceModelCode        = @SourceModelCode       
    ,@SourceEntityClassCode  = @SourceEntityClassCode 
    ,@TargetModelCode        = @TargetModelCode       
    ,@TargetEntityClassCode  = @TargetEntityClassCode 
    ,@MappingRole            = @MappingRole           
    ,@ProcessStepType        = @ProcessStepType       
    ,@Action                 = @Action                
;

SELECT 'ReturnCode: ',@ReturnCode;

DECLARE  @TemplateLanguageCode      NVARCHAR(256)   = NULL
        ,@TemplateClassification    NVARCHAR(256)   = NULL
        ,@TemplateComment           NVARCHAR(1024)  = NULL
        ,@TemplateVersion           NVARCHAR(20)    = NULL
        ,@TemplateDate              DATE            = NULL
        ,@TemplateCodeSnippet       NVARCHAR(MAX)   = NULL
        -- Type of load pattern (e.g. Hub, Sat, PIT)
        ,@LoadPatternId             BIGINT          = NULL
	 -- Action and return code 
        ,@Action                    CHAR(2)         = NULL
        ,@ReturnCode                INT             = NULL
        -- Mapping/LPS (optional)
        ,@SourceModelCode           NVARCHAR(100)   = NULL
        ,@SourceEntityClassCode     NVARCHAR(100)   = NULL
        ,@TargetModelCode           NVARCHAR(100)   = NULL
        ,@TargetEntityClassCode     NVARCHAR(100)   = NULL
        ,@MappingRole               NVARCHAR(256)   = NULL
        ,@ProcessStepType           NVARCHAR(50)    = NULL
;


SET @TemplateLanguageCode   = N'TSQL'

-- Set main body section 
SET @TemplateClassification = N'<#=mainBodySection#>'

-- Use 'R' to read code snippet
--
-- Set type of action 'I', 'U', 'R', 'S' or 'D'
SET @Action                 = 'R'

-- Set load pattern to 1 (Hub)
--
-- Type of load pattern (e.g. Hub, Sat, PIT)
SET @LoadPatternId          = 1


EXEC @ReturnCode = MetadataZoneCore.uspSetCompilerModuleTemplate
     @TemplateLanguageCode   = @TemplateLanguageCode  
    ,@TemplateClassification = @TemplateClassification
    ,@TemplateComment        = @TemplateComment       
    ,@TemplateVersion        = @TemplateVersion       
    ,@TemplateDate           = @TemplateDate          
    ,@TemplateCodeSnippet    = @TemplateCodeSnippet   
    ,@LoadPatternId          = @LoadPatternId         
    ,@SourceModelCode        = @SourceModelCode       
    ,@SourceEntityClassCode  = @SourceEntityClassCode 
    ,@TargetModelCode        = @TargetModelCode       
    ,@TargetEntityClassCode  = @TargetEntityClassCode 
    ,@MappingRole            = @MappingRole           
    ,@ProcessStepType        = @ProcessStepType       
    ,@Action                 = @Action                
;

SELECT 'ReturnCode: ',@ReturnCode;

DECLARE  @TemplateLanguageCode      NVARCHAR(256)   = NULL
        ,@TemplateClassification    NVARCHAR(256)   = NULL
        ,@TemplateComment           NVARCHAR(1024)  = NULL
        ,@TemplateVersion           NVARCHAR(20)    = NULL
        ,@TemplateDate              DATE            = NULL
        ,@TemplateCodeSnippet       NVARCHAR(MAX)   = NULL
        -- Type of load pattern (e.g. Hub, Sat, PIT)
        ,@LoadPatternId             BIGINT          = NULL
	 -- Action and return code 
        ,@Action                    CHAR(2)         = NULL
        ,@ReturnCode                INT             = NULL
        -- Mapping/LPS (optional)
        ,@SourceModelCode           NVARCHAR(100)   = NULL
        ,@SourceEntityClassCode     NVARCHAR(100)   = NULL
        ,@TargetModelCode           NVARCHAR(100)   = NULL
        ,@TargetEntityClassCode     NVARCHAR(100)   = NULL
        ,@MappingRole               NVARCHAR(256)   = NULL
        ,@ProcessStepType           NVARCHAR(50)    = NULL
;


SET @TemplateLanguageCode   = N'TSQL'

-- Override main body section 
SET @TemplateClassification = N'<#=mainBodySection#>'

-- Use 'D' to delete code snippet
--
-- Set type of action 'I', 'U', 'R', 'S' or 'D'
SET @Action                 = 'D'

-- Set load pattern to 1 (Hub)
--
-- Type of load pattern (e.g. Hub, Sat, PIT)
SET @LoadPatternId          = 1


EXEC @ReturnCode = MetadataZoneCore.uspSetCompilerModuleTemplate
     @TemplateLanguageCode   = @TemplateLanguageCode  
    ,@TemplateClassification = @TemplateClassification
    ,@TemplateComment        = @TemplateComment       
    ,@TemplateVersion        = @TemplateVersion       
    ,@TemplateDate           = @TemplateDate          
    ,@TemplateCodeSnippet    = @TemplateCodeSnippet   
    ,@LoadPatternId          = @LoadPatternId         
    ,@SourceModelCode        = @SourceModelCode       
    ,@SourceEntityClassCode  = @SourceEntityClassCode 
    ,@TargetModelCode        = @TargetModelCode       
    ,@TargetEntityClassCode  = @TargetEntityClassCode 
    ,@MappingRole            = @MappingRole           
    ,@ProcessStepType        = @ProcessStepType       
    ,@Action                 = @Action                
;

SELECT 'ReturnCode: ',@ReturnCode;

Valid as of release 3.0.0

Purpose

The purpose of this stored procedure is to compile module-templates and apply design metadata to it — during metadata installation. Sections for a module-template are provided by the ExMeX Framework Core itself or may be added by an ExMeX framework Core administrator with the stored procedure USP - uspSetCompilerModuleTemplate.

Furthermore, this stored procedure creates the possibility for the ExMeX Framework admin to test and execute modules, e.g. after changing a code snippet for a template section — without running a batch for it.

Motivation

The motivation for this stored procedure is driven by different use cases but not limited to these:

• Enable the ExMeX Framework Core to generate modules for different code languages
• Enable ExMeX framework Core administrators to test and execute additional code snippets for a load pattern or a single module

Applicability

The stored procedure is applied each time new design metadata is provided to the ExMeX Framework Core. This usually happens during the metadata installation. But not limited to.

Content

This stored procedure is used to compile module templates, apply design metadata and execute selected modules for testing purposes.

Execution

See also code snippets in the example section below.

EXEC @ReturnCode = MetadataZoneCore.uspCompiler
        -- Load Pattern Id which will be compiled
         @LoadPatternId         = @LoadPatternId 
        -- Language code -> switch for pattern
        -- Optional, global variable is applied. Can be overriden by this
        ,@LanguageCode          = @LanguageCode
        
        -- Compiler options
        -- 'TPL'  - build final template (tpl) - default
        -- 'XDM'  - build template and apply design metadata - compile (eXecute with Design Metadata - XDM)
        -- 'MOD'  - build example modul (LPS) (design metadata applied, executable returned, not yet persisted)
        -- 'XMOD' - execute module
        ,@CompilerExec          = @CompilerExec

        -- Compile results:
        -- * (TPL) Finally compiled template bevore design metadata is applied
        ,@CompilerTemplate      = @CompilerTemplate OUTPUT
        -- * (MOD) Finally compiled module (LPS) with design metadata applied
        ,@CompilerModule        = @CompilerModule OUTPUT

        -- Mapping/LPS (optional)
        ,@SourceModelCode       = @SourceModelCode
        ,@SourceEntityClassCode = @SourceEntityClassCode
        ,@TargetModelCode       = @TargetModelCode
        ,@TargetEntityClassCode = @TargetEntityClassCode
        ,@MappingRole           = @MappingRole
        ,@ProcessStepType       = @ProcessStepType
;

Which parameter provides the usp?

The following blocks list all parameters available for the stored procedure:

N'TSQL'

-- 'TPL'  - build final template (tpl) - default
-- 'XDM'  - build template and apply design metadata - compile (eXecute with Design Metadata - XDM)
-- 'MOD'  - build example modul (LPS) (design metadata applied, executable returned, not yet persisted)
-- 'XMOD' - execute module

SELECT SourceModelCode
 ,SourceEntityClassCode
 ,TargetModelCode
 ,TargetEntityClassCode
 ,MappingRole
FROM MetadataZone.Mapping

EXEC [...]
    ,@SourceModelCode        = N'STAGE_LAYER'
    ,@TargetModelCode        = N'CoreLayerwithHashKey'
    ,@ProcessStepType        = N'DEF'
    ,@SourceEntityClassCode  = N'ArtworkInMuseum'
    ,@TargetEntityClassCode  = N'HubMuseum'
    ,@MappingRole            = N'ArtworkInMuseum2HubMuseum'
    ,@ProcessStepType        = 'DEF'
[...]

Return Codes

This stored procedure returns the values listed below:

Considerations and consequences

With this stored procedure it is possible to compile templates of the ExMeX Framework Core and apply design metadata to it.

Example

-- Decalare and set parameter for usp
DECLARE  @LoadPatternId             BIGINT          = 1
        ,@LanguageCode              NVARCHAR(256)   = NULL
        ,@CompilerTemplate          NVARCHAR(MAX)   = N'nope' --OUTPUT
        ,@CompilerModule            NVARCHAR(MAX)   = N'nope' --OUTPUT
        ,@CompilerExec              NVARCHAR(10)    = N'TPL' 
        ,@ReturnCode                INT             = NULL
;        
        
EXEC @ReturnCode = MetadataZoneCore.uspCompiler
        -- Load Pattern Id which will be compiled
         @LoadPatternId         = @LoadPatternId 
        -- Language code -> switch for pattern
        -- Optional, global variable is applied. Can be overwritten by this
        ,@LanguageCode          = @LanguageCode
        
        -- Compiler options
        ,@CompilerExec          = @CompilerExec

        -- Compile results:
        ,@CompilerTemplate      = @CompilerTemplate OUTPUT
        ,@CompilerModule        = @CompilerModule OUTPUT
;

SELECT  @ReturnCode         AS ReturnCode
       ,@CompilerTemplate   AS CompilerTemplate
       ,@CompilerModule     AS CompilerModule
;

-- Decalare and set parameter for usp
DECLARE  @LoadPatternId             BIGINT          = 1
        ,@LanguageCode              NVARCHAR(256)   = NULL
        ,@CompilerTemplate          NVARCHAR(MAX)   = N'nope' --OUTPUT
        ,@CompilerModule            NVARCHAR(MAX)   = N'nope' --OUTPUT
        ,@CompilerExec              NVARCHAR(10)    = N'XDM' 
        ,@ReturnCode                INT             = NULL
;        
        
EXEC @ReturnCode = MetadataZoneCore.uspCompiler
        -- Load Pattern Id which will be compiled
         @LoadPatternId         = @LoadPatternId 
        -- Language code -> switch for pattern
        -- Optional, global variable is applied. Can be overwritten by this
        ,@LanguageCode          = @LanguageCode
        
        -- Compiler options
        ,@CompilerExec          = @CompilerExec

        -- Compile results:
        ,@CompilerTemplate      = @CompilerTemplate OUTPUT
        ,@CompilerModule        = @CompilerModule OUTPUT
;

SELECT  @ReturnCode         AS ReturnCode
       ,@CompilerTemplate   AS CompilerTemplate
       ,@CompilerModule     AS CompilerModule
;

-- Decalare and set parameter for usp
DECLARE  @LoadPatternId             BIGINT          = 1
        ,@LanguageCode              NVARCHAR(256)   = NULL
        ,@CompilerTemplate          NVARCHAR(MAX)   = N'nope' --OUTPUT
        ,@CompilerModule            NVARCHAR(MAX)   = N'nope' --OUTPUT
        ,@CompilerExec              NVARCHAR(10)    = N'MOD' 
        ,@ReturnCode                INT             = NULL
;        
        
EXEC @ReturnCode = MetadataZoneCore.uspCompiler
        -- Load Pattern Id which will be compiled
         @LoadPatternId         = @LoadPatternId 
        -- Language code -> switch for pattern
        -- Optional, global variable is applied. Can be overwritten by this
        ,@LanguageCode          = @LanguageCode
        
        -- Compiler options
        ,@CompilerExec          = @CompilerExec

        -- Compile results:
        ,@CompilerTemplate      = @CompilerTemplate OUTPUT
        ,@CompilerModule        = @CompilerModule OUTPUT
;

SELECT  @ReturnCode         AS ReturnCode
       ,@CompilerTemplate   AS CompilerTemplate
       ,@CompilerModule     AS CompilerModule
;

-- Decalare and set parameter for usp
DECLARE  @LoadPatternId             BIGINT          = 1
        ,@LanguageCode              NVARCHAR(256)   = NULL
        ,@CompilerTemplate          NVARCHAR(MAX)   = N'nope' --OUTPUT
        ,@CompilerModule            NVARCHAR(MAX)   = N'nope' --OUTPUT
        ,@CompilerExec              NVARCHAR(10)    = N'XMOD' 
        ,@ReturnCode                INT             = NULL
;        
        
EXEC @ReturnCode = MetadataZoneCore.uspCompiler
        -- Load Pattern Id which will be compiled
         @LoadPatternId         = @LoadPatternId 
        -- Language code -> switch for pattern
        -- Optional, global variable is applied. Can be overwritten by this
        ,@LanguageCode          = @LanguageCode
        
        -- Compiler options
        ,@CompilerExec          = @CompilerExec

        -- Compile results:
        ,@CompilerTemplate      = @CompilerTemplate OUTPUT
        ,@CompilerModule        = @CompilerModule OUTPUT
;

SELECT  @ReturnCode         AS ReturnCode
       ,@CompilerTemplate   AS CompilerTemplate
       ,@CompilerModule     AS CompilerModule
;

-- Decalare and set parameter for usp
DECLARE  @LoadPatternId             BIGINT          = 1
        ,@LanguageCode              NVARCHAR(256)   = NULL
        ,@CompilerTemplate          NVARCHAR(MAX)   = N'nope' --OUTPUT
        ,@CompilerModule            NVARCHAR(MAX)   = N'nope' --OUTPUT
        ,@CompilerExec              NVARCHAR(10)    = N'XMOD' 
        ,@ReturnCode                INT             = NULL

        -- Mapping/LPS (optional)
        ,@SourceModelCode           NVARCHAR(100)   = NULL
        ,@SourceEntityClassCode     NVARCHAR(100)   = NULL
        ,@TargetModelCode           NVARCHAR(100)   = NULL
        ,@TargetEntityClassCode     NVARCHAR(100)   = NULL
        ,@MappingRole               NVARCHAR(256)   = NULL
        ,@ProcessStepType           NVARCHAR(50)    = NULL
; 

SET @SourceModelCode        = N'STAGE_LAYER'
SET @TargetModelCode        = N'CoreLayerwithHashKey'
SET @ProcessStepType        = N'DEF'
SET @SourceEntityClassCode  = N'ArtworkInMuseum' 
SET @TargetEntityClassCode  = N'HubMuseum'
SET @MappingRole            = N'ArtworkInMuseum2HubMuseum'

EXEC @ReturnCode = MetadataZoneCore.uspCompiler
        -- Load Pattern Id which will be compiled
         @LoadPatternId         = @LoadPatternId 
        -- Language code -> switch for pattern
        -- Optional, global variable is applied. Can be overwritten by this
        ,@LanguageCode          = @LanguageCode
        
        -- Compiler options
        ,@CompilerExec          = @CompilerExec

        -- Compile results:
        ,@CompilerTemplate      = @CompilerTemplate OUTPUT
        ,@CompilerModule        = @CompilerModule OUTPUT

        -- Mapping/LPS (optional)
        ,@SourceModelCode       = @SourceModelCode
        ,@SourceEntityClassCode = @SourceEntityClassCode
        ,@TargetModelCode       = @TargetModelCode
        ,@TargetEntityClassCode = @TargetEntityClassCode
        ,@MappingRole           = @MappingRole
        ,@ProcessStepType       = @ProcessStepType
;

SELECT  @ReturnCode         AS ReturnCode
       ,@CompilerTemplate   AS CompilerTemplate
       ,@CompilerModule     AS CompilerModule
;