Sql-server – SSDT Schema Compare doesn’t work while a BULK INSERT is in progress

sql serverssdtssis

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.

Best Answer

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.