SSIS package hanging when using transactions

Standard

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’

TransactionOption

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.

TableLock

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.

Advertisements

One thought on “SSIS package hanging when using transactions

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s