I suspect (and from my experience) the deadlock is not guaranteed to go away by breaking the execution into smaller batches. Instead, I suggest to try using first this: set the target table to not to lock in the OLDEDB editor as explained in this article: http://msdn.microsoft.com/en-us/library/ms188439.aspx Please get back to us if you need more troubleshooting. PS: I would ask your DBA admin to profile the database for deadlocking to eliminate the cause from the other/non SSIS package side.Arthur My Blog

Hello, i have ETL which downloads Oracle views' data to Staging tables. There is created 5 Data Flow tasks for each foreign country. 4 country downlods in about 15-20 minutes and the last one takes about 2 hours. Everything was fine. But this is the second weekend i have an error. " Code: 0xC0202009 Source: 5 Country OLE DB Source [1] Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. End Error Error: 2011-01-22 04:36:10.65 Code: 0xC0047038 Source: 5 country SSIS.Pipeline Description: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput()" On this step i changed retry to 3 times. So two times i get this error. And other two times i get another error. He occurs when 5 country data is successfully loaded and executing "Execute SQL task" which write in another table 1 row (the time when step is finished. using getdate() ). Error: " Code: 0xC002F210 Source: Update ProcessLog 5 Country Execute SQL Task Description: Executing the query "update ProcessLog set ProcessLogCountAfter = ..." failed with the following error: "Transaction (Process ID 75) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction."" Now i think to divide load for this country in small bacthes. For example load data 6 months in a loop. What do you think?

I didn't answer because I have tested ETL for 4 weeks. I divided 5 country Load into small batches. Now i don't get the first error (DTS_E_OLEDBERROR, DTS_E_PRIMEOUTPUTFAILED). But the second problem remains.With each Data Flow i load data to staging table. On OLE DB Destination Editor "Table Lock" option is disabled. Only left "Check constraints". Could be this problem? -------- Data Flow 1 ----- --------- Data Flow 2 -------- | | | | --- Update ProcessLog 4 Country ---- --- Update ProcessLog 5 Country ----- Can anyone explain why i get error: " Code: 0xC002F210 Source: Update ProcessLog 5 Country Execute SQL Task Description: Executing the query "update ProcessLog set ProcessLogCountAfter = ..." failed with the following error: "Transaction (Process ID 75) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction."" This task (Update ProcessLog X Country) updates ProcessLog table and only 1 record. If error would be on DataFlow i could agree that problem is with locking. But now i get error in another table. Which contains only about 1000 records.. For that Job i set 4 Retry Attempts. For about 2-3 times i get this error and on the 3th or 4th time loads successfully.

Can anybody help me? :)