11

I'm working at a large ETL and DW project where we use TFS/source control together with both SSIS and SSDT.

Today, I found out that while an SSIS package is performing a BULK INSERT into a database table, it is not possible to perform an SSDT Schema Compare against that database. This is unfortunate, as some of our packages take quite a long time to complete. We want to use the Schema Compare function to detect changes to the database structure in order to save them in our SSDT project for version control of the database.

Looking a little more into this, I found that the Schema Compare function in SSDT executes an SQL script that calls the OBJECTPROPERTY() system function on the tables in the database. Specifically in my case, any calls to OBJECTPROPERTY(<object_id>, N'IsEncrypted') seems to be blocked, when <object_id> refers to a table that is currently being bulk inserted.

In Visual Studio, the SSDT Schema Compare simply times out after a while and claims that no differences have been detected.

Is there a workaround to this issue in SSDT, or should I perhaps try to file a MS Connect bug report?

Alternatively, since the BULK INSERT happens from an SSIS package, is there perhaps some way to make this insertion without locking OBJECTPROPERTY-calls on the table? Edit: In SSIS OLE DB Destinations, we can remove the check mark from "Lock Table", which does what it says, but this might hurt performance in some situations. I am much more interested in a solution that allows the SSDT Schema Compare to do its job, even if some objects are locked.

Improve this question
7
  • Have a look at Controlling Locking Behavior for Bulk Import - you may have 'table lock on bulk load' enabled. Also check your BULK INSERT isn't specifying TABLOCK
    – stuartd
    Commented Apr 28, 2015 at 9:39
  • If you are taking table locks, you might find the load faster if you disable it anyway (technet.microsoft.com/en-us/library/ms177445.aspx) - whatever the cause I would raise a connect because a timeout should at the very least fail rather than just say that there are no changes
    – Ed Elliott
    Commented Apr 28, 2015 at 9:42
  • Thank you for the replies, stuartd and Ed Elliot. It turns out we actually do want to lock the table, for performance reasons. In my opinion, SSDT should be able to handle this, because we only want to compare the database, not apply changes to objects in the database. I will create a connect post to address this.
    – Dan
    Commented Apr 28, 2015 at 13:51
  • 3
    Internals are not my forte but as I understand it, you have a lock on the table. Whatever lock is taken is for the bulk insert is incompatible with the lock(s) required to validate the schema. Relevant reading BOL Schema lock
    – billinkc
    Commented Apr 28, 2015 at 14:34
  • Could you perhaps better explain why the schema comparison must run in parallel with a load operation? Perhaps an alternative is to have a reference model of the database. No data, just schema. Run your comparisons against that and then ensure that no one modifies the actual database where these bulk ops are being performed without first updating the reference model.
    – billinkc
    Commented Apr 28, 2015 at 14:36

1 Answer 1

Reset to default
19

The OBJECTPROPERTY call requires a schema stability (Sch-S) lock, which is only incompatible with a schema modification (Sch-M) lock.

The BULK INSERT will take a Sch-M lock in some circumstances. These are listed in the "Table Locking and Logging During Bulk Import" section of Guidelines for Optimizing Bulk Import in Books Online:

Bulk Import Locking

If the destination table is clustered, you may find enabling trace flag 610 helps. Please read the whole series of those posts and the Data Loading Performance Guide and test thoroughly if you decide to go this route.

I have no idea why SSDT checks the IsEncrypted property for tables. I cannot imagine a scenario where that makes sense, but that's a question for the SSDT people.

Improve this answer
1
  • 3
    This was a very comprehensive and satisfactory answer. Thank you very much.
    – Dan
    Commented Apr 28, 2015 at 19:18

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.