The short story:
I have a data flow inside a For Each container that is inside a sequence container. A transaction is established at the sequence container, with Isolation Level Serializable. The For Each container is limited to execute only 4 iterations. The data flow iterates once fine, but the second iteration hangs. The Activity Monitor shows the process is waiting on a another process with a SID of -2. From other research in the past, I have found that this is an internal process dedicated to DTC (Distributed Transaction Coordinator).
Microsoft SQL Server 2005 (SP3) on Windows 2008 R2 Standard. I'm using SQL Server BIDS 2005 for building these packages. I also have SQL Server 2008 installed and the databases are installed there.
The data flow merges data from the same table in two databases, source and target, using a Merge Join component. It then performs a few transformations and lookups from other tables in the target DB to get IDs. Then it inserts new records into the target DB or updates existing records in the target DB. The two source tables are using OLE DB Source components with customized queries. The insert target component is an OLE DB Destination and the update target component is an OLE DB Command. In this particular scenario,
View Complete Post