I have one SSIS package for 7 clients. The package needs to run for all 7 at once. They all have an execute task that updates the same table. The update has a where clause with a CompanyID variable. So the task only updates the client it is being executed for. I actually update a few fields, but they are all based on CompanyID so I don't mix anything up.
This works fine when I run them one at a time. When I run all 7 at once I get frequent deadlocks. I've tried creating indexes based on the particular fields I'm updating. I'm not sure if that was the right choice. I've also tried changing the IsolationLevel for the entire package and the specific execute task that is causing the deadlock. I'm unable to find the right combination that will stop the deadlocks. Is this not possible?
How do I get this SSIS to complete without deadlocks?
Using: SQL Server 2008 R2