I have some SSIS package that are loading warehouse dimension tables. There are two input flows, one from the source and one from the warehouse table for comparison. There are five output flows that either insert to, or update, the warehouse tables. Occasionally one of these packages hangs part way through processing. Looking at SQL Server it seems it has deadlocked its self. It looks like the input source is holding a lock that's preventing an update, and there it sits indefinitely. For one package I created five tables for each of the output flows, then come back in later in the control flow and use SQL Tasks to apply the intermediate tables to the warehouse. This is a lot of work and overhead, but I had to get one case working ASAP. Is there a way to get SSIS to be better behaved, vis--vis locking and deadlocks, or is there a better design patter for this application?

Hi, Check if this helps..Rajkumar

Yes, I've turned off Table Lock, no it didn't help.

Hi tlum, You can use "with NOLOCK" in T-SQL to avoid deadlocks, but NOLOCK will result in dirty reads, for more information about how to get rid of deadlocks, please refer to: http://msdn.microsoft.com/en-us/library/ms191242.aspx and http://blogs.technet.com/b/fort_sql/archive/2011/12/12/get-rid-of-deadlocks.aspx Thanks, Eileen TechNet Subscriber Support If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here. Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

Hi tlum, You can use "with NOLOCK" in T-SQL to avoid deadlocks, but NOLOCK will result in dirty reads, for more information about how to get rid of deadlocks, please refer to: http://msdn.microsoft.com/en-us/library/ms191242.aspx and http://blogs.technet.com/b/fort_sql/archive/2011/12/12/get-rid-of-deadlocks.aspx Thanks, Eileen TechNet Subscriber Support If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here. Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

You mentioned "I have some SSIS packages ...." check the other packages that might fire off at the same time and are hitting the same table as other packages, i had this issue once. Sincerely Nik -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

NOLOCK is a very bad idea and not an option.

No, only one package runs at a time. SQL Server Process Monitor is very clear about what is blocking what, and profiler gives a detailed view of it. This is a data warehouse application where a dimension is being updated, it is not something exotic. It is standard practice to compare the incoming data against the data in the dimension table and process accordingly which would consist of one of the following: Insert a new record. Insert a new record and update an existing record. Updating an existing record. Reading the dimension table is one source feeding the pipeline. The inserts and updates are against the same dimension table, yes, it is updating the same table its reading. The input blocks the output with a lock. The input continues until the back pressure in the pipeline from its lock shuts it down and there it sits until you kill the database process. Ordinarily the insert/update volume is low and there sufficient memory that the back pressure from the blocked outputs don't cause a deadlock. However, sometimes updates on the source can cause nearly every dimension record to be touched. In this case there is insufficient memory to hold all the output and back pressure shuts down the input which then never releases its lock. One cure has been to send the output to separate update staging tables and then come back after the fact and apply the changes to the dimension once the lock from the read is gone. This however causes an explosion in output staging tables and complicates the dimension update process. In fact, its more complicated than just using MERGE which defeats the purpose of SSIS. I find it difficult to believe that SSIS would not be able to handle one of the most common operations in date warehouse ETL. So, certainly others have gone down this road before me and I want to know what best practice patterns are being employed to do Slowly Changing Dimension updates when working with SSIS architecture.

It appears that READ_COMMITTED_SNAPSHOT will resolve this. Anyone know of an issue with doing this?