I have a SSIS Package which executes few SProcs (on SQL server 2008) , updates a table and exports data in a csv which is FTP'ed.
I have able to successfully execute the package in TEST environment (Hosted on a diff server).
However when I try to execute this package on production , I encounter below error. Any idea what could be causing this? (I have attached the code which calls the SPs from SSIS)
[Execute SQL Task] Error: Executing the query "declare @dt as datetime2
set @dt = SYSDATETIME()
..." failed with the following error: "Transaction (Process ID 94) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.