Xceptor: How to clear Data Sets using a different Message Processor?

How to clear Data Sets using a different Message Processor?

I believe the title might be little confusing, so let me explain it more clearly.

It is a common practice to use the option "Clear existing data before load" when loading new data into a Data Set, as it erases any existing data before the new data is added.

In this article, we will explore a technique for clearing data from a Data Set using a distinct Message Processor, which does not share the same Data Format in the Processing Rules as the Source Format of the Data Set.

Before discussing how to clear data from Data Sets using a different Message Processor, here are the methods for loading data into Data Sets.

How to load data into Data?Sets?

There are 4 ways to load data into Data?Sets. They are:

1. You can manually add data (One record at a time) to Data Sets by clicking on "Add Item" button in the view page.
2. Another way is by clicking on "Import" button to add data from the input file.
3. Create an action in the Message Processor called "Load to Data Set" to load the data into the Data Set from an external input file. The input data goes through all the enrichments defined in the Data Format and gets into the Data Set.
4. Using SQL commands in MS SQL Server such as INSERT & BULK INSERT.

Load to Dataset

This action can be used to ingest data into a dataset, typically you'd see only 3 options to manipulate existing data. You can see additional "Options" in the dropdown only if you set one of the captured fields as a key field in the source data format that's used to define the dataset.

We can enable a Data Set to be used as a Source by Translation Table only when a specific line of code w.r.t the Data Set is already been added in the?Xceptor.config?file.

Here's a sample line of code which must be added in between Translation tags in the Xceptor.config file.

<translation>

<source name="DSTable" sql="select * from DSTable"/>

</translation>

Problem Statement

A Data Set is required to store data that is obtained from multiple files, such as "ABC_1/27/2022_001," "ABC_1/27/2022_002," etc. and the number of files that we get is also inconsistent.

When the trigger file gets dropped (which is the second file) in the input channel, it pulls in the data from the Data Set via Translation Table to the current Data Format and generates a reconciled output.

Solution

Generally, in such given scenarios you've to use "None" as the option in "Load to Data Set" for loading the data into the Data Sets.

However, as there's no action defined to clear data from the Data Set. It doesn't delete data after the reconciliation is completed.

To tackle this, create an action to clear data from the Data Set in the second Message Processor which is used to process the trigger file.

Here are the steps:

1. Firstly, create a mapping between the Input Format of the trigger file to the Input Format associated with the Data Set (Source Format).
2. In the Message Processor, create an action - "Load to Data Set", use "Clear existing data before load" as the option.
3. In the "Filter Items to Load", choose any of the available fields and set it to a gibberish value like "XYZ".
4. Make sure that this action is set as the last action because Xceptor executes the defined actions in the same order as they're available in the Message Processor.

When the trigger file gets picked up by the Xceptor, it goes through all the enrichments such as "Reference Data Lookup" which is used to import data from a Translation Table and generates an output with the reconciled data.

Next, when Xceptor has to process the other action which is "Load to Data Set", it firstly clears existing data from the Data Set then looks into the data available to load into the Data Set which matches the given filtering condition.

Since the incoming data doesn't match with the given condition (gibberish text), Xceptor doesn't push any unwanted data into the Data Set.

You can't clear data from the Data Set without making a mapping between both the Input Formats.

Xceptor will return an error notification - Failed to apply mapping from "Folder Name/Data Format Name" (Data Format of the trigger file) to "Folder Name/Data Format Name" (Data Format of Source Format of the Data Set).

Ensure the mapping has been made from the Data Format of the trigger file to the Source Format of the Data Set (Source -> Destination). Xceptor will return the above stated error even if it's mapped the other way round like Destination -> Source.

?

?

????????????????????????????????????????