Deadlock in Dataflow

Hi all,

I have encountered a SQL 2005 deadlock issue while executing dataflow in a SSIS package. The deadlock happens when I have indexed two columns. If I don't have index, deadlock does not happen.

Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Transaction (Process ID 67) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.".

The above causes the rest of the dataflow execution to be terminated.

What my dataflow does is to extract data from 14 flat files and then insert the records into a single table (no primary key, but with two columns indexed).

Can anyone please advise how I can avoid deadlock with indexes in a table?

Thank you and much appreciated!

Before the data flow, in the control flow, I'd issue an Execute SQL task to disable the indexes on the table. Then after the data flow, I'd re-enable them. Should help with performance as well.|||

Hi Phil,

Thank you very much for your response.

Would you mind tell me how do I disable and re-enable indexes (the SQL command/syntax ?) in a SQL task?

Thanks again.

|||http://msdn2.microsoft.com/en-us/library/ms177406.aspx

To disable:
ALTER INDEX your_index_name ON your_table_name DISABLE

To reenable:
ALTER INDEX your_index_name ON your_table_name REBUILD|||

I tried the ALTER INDEX ... DISABLE command and also in SQL Server Management Studio manually right-clicked INDEX folder to "disable all" index, but they didn't seem to disable the index. Deadlock still occurs.

Did I miss something?

Thanks!

|||

I think I got this deadlock solved.

Not sure why disabling index does not work for me, but instead of disabling it, I drop the index before the data load. After finishing data loading, I re-create the index and rebuild it. It works fine that way.

Thanks Phil!