To delete the records from Target table during Transform mapping

[rajalakshmi12]

Hi All,

I have a scenario. Can anyone please help !

In the Source excel sheet, I have 100 records (Coalesce used) to insert it for the very first time. Second day, I have only first 50 records to be updated which is already sourced the previous day. Now, we would like to delete the other 50 records during transform mapping, which is not available on the source sheet, but available in the target table.

Please let me know what are the ways to fulfill the requirement.

Thanks & Regards,

[Mateen]

If you make sure that second day records have changes to the fields, then write an OnComplete script in transform map to get all the records that were updated before today and delete them.

The reason why I want to make sure that day 2 records have some change in data, coz transform maps ignore the row if there is no change in fields. In this case updated date time will be not be changed. Only way to mitigate is to have a field which you update everytime when you run the row transform.

[Harshinya1]

My suggestion would be,

1. Create an active field on the target table

2. Create a onBefore transform script to set the active attribute to false on all records

3. During transformation,when record is being updated, set the active attribute to true

4. After completing the transform, you can either write an onComplete transform script or a scheduled job to delete the inactive records that have not been touched during the transform.

Hope this helps,

Thanks,

Harshinya

[Val_rio Assola1]

Thanks to share your idea