onComplete transform map script

[Chandu12]

Hi Experts,

Could someone help me in my requirement.

We have a Informatica load(Weekly), we need to implement clean-up logic for the staging table ex:(u_master_load).

The logic is the following: If a load is completed successfully (no errors), the staging table records should be deleted so that only the current and the last successful load remain.

Ex:-1) ISET013001, 2) ISET013002 3) ISET013003 4) ISET013004

We have 4 import set out of that i have to keep the current import set and the previous import set, apart from that need to delete other records in the staging table which are completed.

Logic implemented: Oncomplete script, need to implement using the objects (log, source etc). Please help me in the coding part.

Regards,

Chalan

[Chuck Tomasi]

Can I ask what the requirement is here? What business value does this bring? Normally, you don't need to be concerned with this as the import set cleaner takes care of old import sets for you after a week (by default).

Look under System Import Sets> Import Set Tables> Scheduled Cleanup

[Chandu12]

Hi Chuck,

thanks for your answer..!!

we need to delete the records in the staging table of the respective import set run (current import set records and last but one import set records should be there), not the Import set.

In every single load we do receive 103,302 records and our staging table records keep growing. so we need this records to be deleted by keeping latest 2 import set.

regards,

Chalan

[Chuck Tomasi · Accepted solution]

I disagree that you need to keep this level of detail on your import set tables. They'll be cleaned as necessary.

Part of the beauty of using a cloud service like ServiceNow is that you don't have to worry about storage so much. 103K records is not that big of a deal for us.

If you have other concerns, let me know, but wouldn't bother writing something custom to maintain import tables when you don't have to.

[Chris M3]

I had to implement a similiar solution for our SCCM Software import (2.5 million records every day).   What Chuck pointed out (ImportSetCleaner) is the best way to accomplish this.

Look at the script in the Scheduled Cleanup under Import Sets, and then take a look at the script include it calls ImportSetCleaner.  You can initialize that object with a table name to do a cleanup of just that table.  use setDays() function to set it to the appropriate time frame.

As for determining whether the import was successful or not, I have not done anything along those lines.