Receiving the following error in step failure of an agent job: SSIS Error Code DTS_E_OLEDBERROR.
An OLE DB error has occurred. Error code: 0x80004005.
Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x00040EDA  
Description:
"Warning: Null value is eliminated by an aggregate or other SET operation.".


Please help to find the root cause for this temporary failure and that what can be done to avoid this in future.
Annoying isn't it. I've done some research and it turns out it is incorrectly reporting a ANSI warning as an error (resolved in SQL Server 2012, no patch available for previous versions).

One way to stop the error is to go through all your code, and wherever you have an aggregate function (e.g. SUM), use ISNULL to ensure the aggregated value can never be NULL:

i.e. change 

SUM(Field1+Field2)


to 

SUM(ISNULL(Field1,0)+ISNULL(Field2,0))


This makes the error go away but might be impractical.