Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2005 Forums  SSIS and Import/Export (2005)  "OLEDB Source task" LOCKS "Execute SQL statement"

Author  Topic  predi Starting Member 2 Posts Posted - 2009-06-15 : 07:22:59 Hi, I strangely encountered the LOCK in my ETL packages. There is an OLEDB source task that reads data from table1, and there is an Execute SQL statement which updates the same records in table1 (There are 6 lookup tasks and 1 conditional split task in between). The source task reads aggregated rows using GROUP BY, and Execute SQL st. task then updates these rows(non aggregated). It happens so that Source task LOCKS update, and because there are more than 100 000 rows, and in one buffer there are around 10 000 rows, the Source task doesn't read the next buffer while update is not finished, and update cannot finish because select is not finished, so I end up with DEAD LOCK. Using NOLOCK on select is not a solution (though is solves the problem). Can anybody help? And one more thing - it happens not always, just in some strange arrangment of data, which I cannot determine....