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.