0

SSIS is not really my forte. I noticed you have a table lock option on OLE DB destinations where the entire table becomes locked in what I assume is during the transaction of inserting data.

What happens if you have a flat file data source with a conditional split that is parsing out data into 5 or more OLE DB destinations that are targeting the same table with table lock on? Would each OLE DB destination get blocked by one another in this scenario if data is being fast loaded (inserted) into the destinations themselves?

I removed the table lock in my instance and everything seemed fine. It was splitting the data and inserting records at about 1 million records per minute.

1 Answer 1

1

The table lock option facilitates minimal logging, reducing transaction log space requirements under certain circumstances. Below are relevant excerpts from the Prerequisites for Minimal Logging topic in the SQL Server Books Online.

  • The table is not being replicated.
  • Table locking is specified (using TABLOCK).
  • Table is not a memory-optimized table.
  • If the table has no indexes, data pages are minimally logged.
  • If the table has no clustered index but has one or more nonclustered indexes, data pages are always minimally logged. How index pages are logged, however, depends on whether the table is empty:

    • If the table is empty, index pages are minimally logged.
    • If table is non-empty, index pages are fully logged.
  • If you start with an empty table and bulk import the data in multiple batches, both index and data pages are minimally logged for the first batch, but beginning with the second batch, only data pages are minimally logged.
  • If the table has a clustered index and is empty, both data and index pages are minimally logged. In contrast, if a table has a clustered index and is non-empty, data pages and index pages are both fully logged regardless of the recovery model.

The table lock will block other processes using the table, including the other OLEDB Destinations within the SSIS package. You could set the Maximum Insert Commit Size to improve concurrency but with the consideration that file will be partially imported in the event of a failure.

1
  • Table has no indexes to improve insert speed. The recovery mode is simple, so filling up logs is not a huge issue. It sounds like the answer to my question is that table locking does block the other transactions so setting no table lock is a good option. I've imported quite a lot of data with no issues thus far with table lock off in simple recovery mode.
    – Fastidious
    Nov 21, 2015 at 14:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.