Hi Guys, I am working on a package to load a fact table "A". In my Data Flow Task I am comparing the latest loaded and latest updated records from Staging area with already existing records in Fact table "A". To compare I am using CozyRoc table difference transformation to detect changes. And then accordingly the data is inserted into fact table "A". Problem lies with Select statement blocking Bulk Insert. Since I am using OLEDB Source to select data from fact table "A" and after comparing inserting into same fact table "A", it is causing the select statement blocking the bulk insert statement prepared by OLEDB Destination. I can see the spids in activity monitor and have verified the scenario. I have tried using NO LOCK hint in select statement in my OLEDB source sql query, but no luck. It justs enters into deadlock. Any guidance will be helpful.Thanks, Pulkit Ojha Microsoft Business Intelligence Developer

Hi Pulkit try to put 'SET ISOLATION LEVEL READ UNCOMMITTED' at the beginning of the SQL statementArthur My Blog

Thanks Arthur. But wouldn't that allow dirty reads? Reason being I am inserting new rows into the same table from where I am reading data. Thanks, Pulkit Ojha Microsoft Business Intelligence Developer

Sure it will, then revise your design, you simply need to allow the bulk insert to complete.Arthur My Blog

I tried increasing the DefaultBufferMaxRows and it worked !!! But I guess that is just masking the issue coz record count could increase at any point of time in future. Are you sure I would have to consider changing my design? Thanks, Pulkit Ojha Microsoft Business Intelligence Developer

Yes, it is unreliable.Arthur My Blog

Thanks a lot. Do you have any suggestion on how I should proceed? Only way I can think of is to insert the records into a temporary table first and then insert back to original fact table. Thanks, Pulkit Ojha Microsoft Business Intelligence Developer