Cleaning up transformed rows right after transform completion

[nikita_mironov]

Hi
Hope it will be useful for somebody.
We have faced a situation that after running Service-Now for several months several import set tables had grown to unmanageable size. Those tables were used for huge data uploads that happened _every_ day.

My first idea was to enable Scheduled Import Sets Cleanup (see http://wiki.service-now.com/index.php?title=Import_Set_Scheduled_Cleanup).
This solves the issue but puts a constraint. The scheduled cleanup is enabled for _all_ import set tables. You can not specify that certain import set table (e.g. some integration or other important things) is not cleaned up according to different schedule than other less important import set table that consumes a lot of DB storage.

Here is "OnComplete" Transform Map script (see http://wiki.service-now.com/index.php?title=Transform_Map_Scripts) that runs at the end of an import run, after all data rows are read and transformed. It simply deletes all import set rows that have been just transformed. The Transform Map Script uses "import_set" object available for "OnComplete" Transform Map script and out of the box Script Include "ImportSetCleaner" (with no customization). I find it very useful when you are processing thousands/millions of records and do not expect the necessity of troubleshooting, so you can drop them immediately after processing (import set, import set history are kept in the system by the way, only import set rows are dropped):

/**
* Delete import set rows on transform completion
**/
var ic = new ImportSetCleaner();
ic._cleanTable(import_set.table_name, 'sys_import_set=' + import_set.sys_id);

Of course, Scheduled Cleanup (http://wiki.service-now.com/index.php?title=Import_Set_Scheduled_Cleanup) should be enabled anyway, but like in my case it can be scheduled to clean import sets older than XX days while huge import sets are emptied from rows immediately.

Attached is the screenshot illustrating the implementation.

Any comments/suggestions are welcome.

P.S. Read carefully http://wiki.service-now.com/index.php?title=Import_Set_Scheduled_Cleanup before you proceed with scheduled cleanup setup - you may need to get in touch with Customer Support if your sys_import_set_row table contains too many rows.

Message was edited by: Lawrence Eng Missing screenshot re-attached.

[acoates1]

Really helpful, having exactly this issue on our instance.

[gsnow]

Hi Nikita,

Im having an requirement:

Im getting multiple jobname from external system. Im getting the job name from DB with transform map.

Issue: External system is renaming the jobname often and the renamed new jobname will be added with the job table. Now the job table is containing both old job name and new job name as well.

I need to delete the old jobnames now. What i thought is i can delete the old job name for certain period.

In short I need to delete the old jobnames which was imported before (for eg: 3days ago).

so can you please give any suggestion?

Because I'm not that much familiar with transform scripts and scheduled jobs.

Thanks In Advance

[nikita_mironov]

Good day,

I do not think this is related to the topic. Please can you open a new thread for your question.

[nikita_mironov]

I'd look at this in this way (assuming you do not have a way to identify a job other that using it's name that may unfortunately change). Have an onComplete transform script that queries import set table for rows that have been just processed within your import. To know the import set sys_id you may still use "source" object in onComplete transform script, it will refer to the last import set row. Having queried those import set rows you have a clear picture on what target record (target_sys_id) had been processed. Simply delete (inactivate) the remaining target table rows (those that had not been provided in your import set).

[Yadin]

This gets close to a functional answer for the apparent deficiency of SN missing an import coalesce option or any built in option to clean the table before a new import.  However, I want to leave the import table contents for troubleshooting until the next import.  So, how can I cleanup (delete all data) in the import table at the START of the import operation (scheduled) before the new file import begins?