uspRegisterModule

Purpose

The purpose of this stored procedure is to register, update, or delete modules in the ExMeX Framework Core. This procedure enables orchestration capabilities by allowing modules to be activated, deactivated, and configured for load window usage.

Motivation

uspRegisterModule provides comprehensive module management functionality within the ExMeX Framework Core. It supports three primary operations: Add (A), Update (U), and Delete (D) modules, with the Update operation being fully implemented for production use.

Applicability

The stored procedure uspRegisterModule registers modules in the ExMeX Framework Core as data logistic processes.

Syntax

EXEC MetadataZoneCore.uspRegisterModule
     @RegisterModuleExec    = 'U'
    ,@ModuleId              = @ModuleId OUTPUT
    ,@BatchId               = @BatchId OUTPUT
    ,@ModuleIsActive        = NULL
    ,@ModuleUsesLoadWindow  = NULL
    ,@ModuleUsesRowsRead    = NULL
    ,@BatchLabel            = NULL
    ,@SourceModelCode       = NULL
    ,@SourceEntityClassCode = NULL
    ,@TargetModelCode       = NULL
    ,@TargetEntityClassCode = NULL
    ,@MappingRole           = NULL
    ,@ProcessStepType       = NULL
    ,@Debug                 = 0

Parameters

Required Parameters

Parameter	Data Type	Description
@RegisterModuleExec	CHAR(1)	Operation type: 'A' (Add), 'U' (Update), 'D' (Delete)

Module Identification Parameters

Choose one of the following approaches to identify the module:

Option 1: Direct ID Reference

Parameter	Data Type	Description
@ModuleId	BIGINT OUTPUT	Unique identifier of the module

Option 2: Natural Key Mapping

Parameter	Data Type	Description
@SourceModelCode	NVARCHAR(128)	Source object model identifier
@SourceEntityClassCode	NVARCHAR(128)	Source data object identifier
@TargetModelCode	NVARCHAR(128)	Target object model identifier
@TargetEntityClassCode	NVARCHAR(128)	Target data object identifier
@MappingRole	NVARCHAR(256)	Name of the object mapping
@ProcessStepType	VARCHAR(50)	Process step type: 'DEF', 'SSIS', or 'CDC'

Batch Identification Parameters

Choose one of the following approaches to identify the batch:

Parameter	Data Type	Description
@BatchId	BIGINT OUTPUT	Unique identifier of the batch
@BatchLabel	NVARCHAR(256)	Name/label of the batch (alternative to BatchId)

Update Payload Parameters

Parameter	Data Type	Default	Description
@ModuleIsActive	BIT	NULL	Module activation status (0 = disabled, 1 = active)
@ModuleUsesLoadWindow	BIT	NULL	Load window usage flag (0 = no, 1 = yes)
@ModuleUsesRowsRead	BIT	NULL	RowsRead functionality flag (0 = no, 1 = yes)

Optional Parameters

Parameter	Data Type	Default	Description
@Debug	TINYINT	0	Debug mode indicator (0 = off, 1 = on)

Return Values

The procedure returns an integer status code:

• 0: Success
• 8: Update operation failed (transaction rolled back)
• 11: Module and/or batch combination does not exist

Usage Examples

Example 1: Update Module Using Direct IDs

DECLARE @ModuleId BIGINT = 12345
       ,@BatchId  BIGINT = 67890
;

EXEC MetadataZoneCore.uspRegisterModule
     @RegisterModuleExec    = 'U'
    ,@ModuleId              = @ModuleId
    ,@BatchId               = @BatchId
    ,@ModuleIsActive        = 1
    ,@ModuleUsesLoadWindow  = 0
    ,@ModuleUsesRowsRead    = 1
;

-- Verify the update
SELECT ModuleIsActive
      ,ModuleUsesLoadWindow
      ,ModuleUsesRowsRead
      ,ModifiedBy
      ,ModifiedAt
FROM [MetadataZoneCore].[BatchExecuteLoadProcessStep]
WHERE [ProcessStepId] = @ModuleId
  AND [LoadBatchId]   = @BatchId
;

Example 2: Update Module Using Natural Keys

DECLARE @ModuleId              BIGINT        = NULL
       ,@BatchId               BIGINT        = NULL
       ,@SourceModelCode       NVARCHAR(128) = N'SourceSystem'
       ,@SourceEntityClassCode NVARCHAR(128) = N'Customer'
       ,@TargetModelCode       NVARCHAR(128) = N'DataSolution'
       ,@TargetEntityClassCode NVARCHAR(128) = N'CustomerLDA'
       ,@MappingRole           NVARCHAR(256) = N'CustomerSourceToStageMapping'
       ,@ProcessStepType       VARCHAR(50)   = 'DEF'
       ,@BatchLabel            NVARCHAR(256) = N'DailyCustomerLoad'
;

EXEC MetadataZoneCore.uspRegisterModule
     @RegisterModuleExec     = 'U'
    ,@ModuleId               = @ModuleId
    ,@BatchId                = @BatchId
    ,@SourceModelCode        = @SourceModelCode
    ,@SourceEntityClassCode  = @SourceEntityClassCode
    ,@TargetModelCode        = @TargetModelCode
    ,@TargetEntityClassCode  = @TargetEntityClassCode
    ,@MappingRole            = @MappingRole
    ,@ProcessStepType        = @ProcessStepType
    ,@BatchLabel             = @BatchLabel
    ,@ModuleIsActive         = 1
    ,@ModuleUsesLoadWindow   = 1
    ,@ModuleUsesRowsRead     = 0
    ,@Debug                  = 1
;

-- Verify the update
SELECT ModuleIsActive
      ,ModuleUsesLoadWindow
      ,ModuleUsesRowsRead
      ,ModifiedBy
      ,ModifiedAt
FROM [MetadataZoneCore].[BatchExecuteLoadProcessStep]
WHERE [ProcessStepId] = @ModuleId
  AND [LoadBatchId]   = @BatchId
;

Implementation Status

Operation	Status	Description
Add (A)	Not Implemented	Placeholder for future module registration functionality
Update (U)	✅ Fully Implemented	Complete update functionality with transaction support
Delete (D)	Not Implemented	Placeholder for future module deletion functionality

Key Features

Transaction Safety

The Update operation is wrapped in a transaction with comprehensive error handling, ensuring data consistency and providing detailed error information in case of failures.

Flexible Module Identification

Modules can be identified either by their unique ModuleId or through natural key mapping using source/target model codes, entity class codes, mapping roles, and process step types.

Flexible Batch Identification

Batches can be identified either by their unique BatchId or by their human-readable BatchLabel.

Selective Updates

Only non-NULL payload parameters are updated, allowing for partial module configuration changes without affecting other settings.

Comprehensive Logging/Debugging

The procedure provides extensive console output including:

• Operation headers with timestamps
• Step-by-step progress indicators
• Parameter validation results
• Transaction status updates
• Success/error summaries
• Debug information when enabled

Debug Support

When debug mode is enabled (@Debug = 1), the procedure provides:

• Detailed parameter information
• Resolution status for BatchId and ModuleId
• Additional execution details
• Final module configuration display

Error Handling

The procedure includes comprehensive error handling:

• Parameter Validation: Ensures the specified module and batch combination exists before attempting updates
• Transaction Management: Automatic rollback on errors with detailed error reporting
• Error Information: Returns SQL Server error details including error number, message, severity, state, procedure, and line number

Dependencies

This stored procedure interacts with the following ExMeX Framework Core objects:

• MetadataZone.LoadBatch - For batch label resolution
• MetadataZoneCore.HubLoadProcessStep - For module natural key resolution
• MetadataZoneCore.BatchExecuteLoadProcessStep - Main target table for updates

Version History

• Version 1.0.0 (2025-09-19)
  • New: Feature 0000686 - Orchestration: Enabling and disabling modules
  • New: Feature 0000714 - Activate load window per module
  • New: Feature 0000713 - Deactivate RowsRead
  • Enhanced: Comprehensive PRINT output and logging
  • Enhanced: Extended debug functionality
  • Initial implementation with Update operation support