I am inserting records by using foreach loop in SSIS.I am using a foreach loop within a for each loop the first foreach loop gets id from a table and based on that id inserts rows in another table, then the other foreach loop uses the id from the old table and the id from the new table to insert records in another table the number of times that id is referenced in another table and grabbing some records from that table as well.
The problem is the number of original records are arouund 20,000 and these records are referenced around 20 times in another table. so 20,000 x 20 = 4,00,000 records needs to be created total in the table in the innermost loop. But when it starts to insert records it is very fast and as the time progresses it becomes slower and slower and eventually it crashes. What should I do? Is there a property in foreach loop that after every n number of inserts it flushes it's memory after making an insert?