SSIS package is failing due to deadlock issue

SSIS and Import/Export
1

Hi ,

One of my scheduled SSIS package is failing daily with below error on one table.

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

When i goggled it some one mentioned to use :WITH (NOLOCK) in source Select query, i did this, Still issue alive and throwing same error

Can anyone suggest on this deadlock issue?

2

My advice is "Don't EVER - NOT EVER - use NOLOCK" The consequences are potentially catastrophic for users

I don't know anything much about SSIS I'm afraid. You may be doing something complex, and as such my advice wouldn't be any use, but for all DAta Import type actions we first load all the data into "staging tables" and then apply the data to the actual, live, tables. That gives us a lot of control as to TRANSACTION BLOCKS and locking tables (if we need to) to ensure that our import, and any update operations, are truly atomic.