Flagging deleted records in destination when source row is missing

Hi,I am looking for guidance on how to implement a soft delete logic using the Advanced ETL tool.My environment consists of a source table and a destination table residing on two different servers. I am successfully inserting new records, but I need to handle deletions. Specifically, if a row exists in the destination but is missing from the source, I need to update the destination table by setting the is_deleted column to 1. I have a unique key, vg_itemcolor, which matches records between both tables.I attempted to use the Lookup transformation, but this seems designed to handle incoming rows (finding matches or non-matches from the source). I am struggling to figure out how to use it to identify rows that are absent from the input stream entirely. I also tried logic stating that if the value is not found, set is_deleted to 1, but since the deleted row does not exist in the source query, the transformation never runs for that ID.Is it possible to detect these missing rows purely within the AETL transformation flow given the cross-server constraint? Or do I need to use a different approach like a full outer join or staging tables?I have attached the source data query, the destination table schema, and screenshots of my current mapping and lookup configuration.
the following is the source query that i have written in lookup.
SELECT

    LOWER(
        TRIM(er.OurRef) + '-' + er.Item + '-' +
        COALESCE(NULLIF(er.FabColor, ''), 'All Colors')
    ) AS vg_itemcolor
FROM dbo.ExpRequirement AS er
JOIN dbo.EXPOHEAD AS eh ON er.OurRef = eh.OurRef
JOIN dbo.ItemMast AS im ON im.ItemCode = er.Item AND im.ItemType = er.ItemType
WHERE eh.OrderDate >= '2025-04-01'
  AND eh.HomeLocation IN ('M000000011')
  AND im.parameter9 NOT IN ('packing material', 'admin')
  AND er.YFProcess = ''

The following is the destination table also this the query in reader 
SELECT
    vg_itemcolor,
    is_deleted
FROM items.Eowise.exp_req 

     

Hi Onkar

There are several ways to do it 

My environment consists of a source table (1) and a destination table (2) residing on two different servers.

1 You can use "Is value in the database" 

www.etl-tools.com/wiki/advanced-etl-proc...lue-in-the-database/

Point reader and writer to the same table (destination table (2))
Point Is value in the database" to  source table (1)

This works well if you have small number of records.

2 use staging table.
load data into staging table and perform update using SQL After

I would use second approach
 

1. So its working with the staging table approach

2. It hangs with Is in Database , and marks everything as wrong output

3. I am also not able to get it work with JOINER table and I tried left outer , right outer join but all methods fail ion the same.

2. It hangs with Is in Database , and marks everything as wrong output

I might hung if you have a lot of records, because it takes time to load them into the memory.

 

Having thought about it we will create a video about soft delete next year.
it will benefit a lot of users.