Again, I’m adding loads of data to a table that I want to roll back when a failure occurs, the data gets loaded by day for a number of days, so a ForEach sequence container was used and its Transaction Option is set to ‘Required’
When I was inserting into my destination, for speed, I had the Table Lock checkbox checked, meaning that the insert would obviously lock the table.
So? What’s the problem?
The problem was that after each insert into the table, the Table Lock wasn’t being dropped, causing my whole SSIS package to grind to a halt.
The only way I could see to resolve the issue was either to do the inserts without a transaction, or to remove the Table Lock from the destination. This all worked fine until there was an error in the source data causing one of the records to redirect to an error table, again, which had Table Lock specified. The answer was to remove Table Lock from all tables inside the transaction.