when the job failed I looked into the history on one of the latest failure its giving message
Could not create an acceptable cursor. [SQLSTATE 42000] (Error 16955) OLE DB provider "SQLNCLI" for linked server "LNK_SERVER" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". [SQLSTATE 01000] (Error 7412). NOTE: The step was retried the requested number of times (5) without succeeding. The step failed.