Search code examples
sql-serverssissql-server-2016ssis-2012

SSIS Table Locking on Transactions

I am trying to upload data into MSSQL 2016 using SSIS.

The process is:

SSIS Process

What I am trying to do is rollback all data if anything fails. First I tried using TransactionOption=Required but this locked the target tables until the SSIS sequence had completed. I have then tried using SQL Tasks to create BEGIN, ROLLBACK and COMMIT transactions as I read this is preferred by some and it does not lock the tables. However, my tables are still locked. I am unable to SELECT data from the tables involved until the sequence has completed.

Transactions:
BEGIN TRANSACTION T1;
ROLLBACK TRANSACTION T1;
COMMIT TRANSACTION T1

Is what I am trying to do even possible and if so what is the best way to do it?

Solution

  • Setting TransactionOption=Required is the same as issuing a TSQL BEGIN TRAN except that you are responsible for implementing your own commit/roll back logic. SSIS may use the Distributed Transaction Coordinator, DTC, or issue begin tran statements directly to your SQL Server instance.

    As the comments indicate, SQL Server will attempt to lock the whole table if it's pouring in great quantities (5k) of rows. This is desirable because SSIS is a high performance ETL tool and I don't want to play nicely with other processes - I have a ton of data to load and everyone else get outta the way.

    I am unable to SELECT data from the tables involved until the sequence has completed.

    You're likely using the default isolation level in your client - for SSMS it is read committed. Maybe your app can handle "dirty reads" and all that that implies.

    If you cannot handle dirty reads, stage your data to a different table (using your data flow task) and when that is complete, use an Execute SQL Task to issue an insert statement. I usually have an explicit tablock hint at this point because I know I'm loading lots of data and I try to help the engine. The benefit of the staging table approach is that it eliminates network traffic as a factor in holding the table lock longer than is optimal.