Transform Map remove records that no longer exist from destination table

[Katie A]

Hello,

I am building an import table that is populated by an external script.

The Transform Map maps the data from the Import table to the Destination table.

The import will run once a day.

Question: How can I remove the data from the Destination table when the record no longer exists at the source? In other words, an existing record is removed from the source and will no longer appear in the Import table the next day. How can I then remove that record at the destination using a transform map script?

[Community Alums]

Hi kda617,

So you are importing the full set of data each day and want to be sure all data is fresh from the import? I have two thoughts here:

• Run a script that deletes all the records from the table prior to import and then the imported data is clean with just the records you've imported. This is the easiest way to do this, but it isn't going to work if you need to be selective about what you remove.
• Either within your data set or with a separate "for deletion" data set, list the records you want to delete and include some marker for deletion and include a script that deletes those rows when it matches up instead of importing.

I have not done either of these, so it is a bit of a thought experiment, but either should help you achieve your goal here, depending on how you want it to work.

Hope this helps,

Ben

[Erik Stolberg]

If you know that all of the import data will be updating your destination records each time, you could run a query after the import is complete to look for records that weren't updated within the last day or even hour and delete them via script.

You could also create a flag on your record destination table, run a scheduled job to clear the flags from all records every day prior to import, then on import using a Transform Map Script set fields to true, and then key off any records that aren't set to true for your delete query.

[Katie A]

Thanks! The import runs every hour so I have a scheduled job that runs every hour and checks if the record hasn't been updated in the past 2 hours. I will probably also need to see what user it was "updated by" as well but I think I am on the right track.

[Katie A]

Is there a way to "force" the transform map to update existing records?

I am marking records for deletion that haven't been updated recently.

However, the problem I am running into is that if values on an existing record haven't changed, then the Updated Date won't change even if the record is in the Import Set.

I am seeing some false positives -- some records that still exist but haven't been updated recently so they are getting flagged for deletion.

If I can force the Updated Date to update each time the Transform Map runs then I should be able to use the Updated Date to mark items for deletion reliably.