Exclusive Intent Locks on RetainSameConnection False

Question

Hi All

I have an SSIS package in which I only have 1 Connection, and it must have the RetainSameConnection property as False; due to the amount of data involved, I need to run parallel threads, sometimes on the same table, over different connections.

The SSIS package runs in 2 phases.  I need to close all the Connections created in phase 1 once done, as it locks the tables I need to reuse in phase 2, even though no work is still being done by the connections in Phase 1.

I'm no DBA and have little knowledge of SSIS, but here is how I came to the above conclusion:

• Disable Phase 2 - Phase 1 runs successfully
• Disable Phase 1, Enable Phase 2 - Phase 2 runs succesfully, as Phase 1 completed successfully in the previous run
• Running the entire package, Phase 2 fails (or rather "hangs"), as there are Exclusive Intend Locks on all Tables used in Phase 1
• Adding a Pseudo step between Phase 1 and 2, adding a break point on it, running the package, killing all connections created in Phase 1 when it hits the break point, and then running phase 2, everything is successful (this took about all my Saturday to figure out)

I have tried changing the Isolation Levels for the tasks in Phase 2 to ReadUncommited.  Dirty Data is not a problem, as Phase 1 has to be complete before Phase 2 starts, and nothing else writes to the tables Phase 1 populates for Phase 2 to use. No luck.

I don't simply want to create a task to kill all sessions of the SSIS package, as the criteria I used in my script to do so, is not something I am confident will work on a different server/environment.

I should add that Phase 1 runs outside of Transactions (i.e. all sequences have Transaction Option "NotSupported"), and Phase 2 runs Stored Procs, all of which creates its own Transactions within the SPs, all with unique Transaction identifiers.

Any suggestions on how to get rid of the Exclusive Intend Locks before Phase 2 starts?

Answer 1

Hi NoRollback,

Have you consider using "NOLOCK" on source queries in your SSIS package, please refer to Todd's reply in the following link:
http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/9c83ae79-790b-44e5-a33a-eea0af65c30f

Thanks,
Eileen