SSIS process having insert blocking the select same table -

i have ssis package queries both source , target tables, full-outer-join merge-join , uses conditional split detect differences insert/update/delete accordingly. done 80 tables , part process completes successfully. though, have had issues in job hangs because of blocking queries.

the process hang if data bulk inserted target table before target data source has completed retrieving existing data.

is there way can have process wait until existing data queried before data inserted. or there better strategy handling inserting data target table while table read.

having done bunch of these, have moved more complex pattern performs lot better. others may differently, works me.

first, instead of merge join, use lookup transformation. lookup transforms set full caching (the default) load data before data flow executes. should identify inserts , updated quickly. inserts can run directly data flow, updates...

the real way updates part of data flow use oledb command transform. these notoriously slow. instead, write data updates temporary cache table in data flow, add execute sql item control flow updates records in target table caching table.

don't forget execute sql item truncate cache table.

finally, identify deletes data flow , using select target table , lookup transform against source table. again, write cache table , delete batch execute sql command.

so control flow has execute sql truncate cache tables, data flow inserts , caching updates, execute sql perform updates, data flow cache deletes, , and execute sql perform deletes. if package work more 1 source table, put control flow items each target table sequence container. not necessary, helps me see logical structure.