Hi all, my package is failing every now and then on the following 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: 0x00040EDA Description: "Warning: Null value is eliminated by an aggregate or other SET operation.". yes, I do have a null aggregation, but in all my tests this didn't cause a failure... anyone had experience with this? Thanks.

You can't aggregate a NULL value. A NULL is nothing, not a number, not a character, it cannot be summed, etc...Can you shed some light on how you have your package built? List the components you're using.

a very simple Data Flow: 2 components - ole db source and destination.source is sql2005, destination the same. what I don't understand is how comes sometimes it works, and sometimes don't. can I set ANSI NULS settings in the connection that might help?

Trying using ISNULL in the source query to replace NULL values with a default value. Are there any other errors or warnings?

The ISNULL is notan option to me - I cannot change the script... No, there are no other warnings...

Liran Russo wrote: The ISNULL is notan option to me - I cannot change the script... No, there are no other warnings... Why not? You have to write the query in the OLE DB Source, so why can't you use ISNULL?

there's a script that runs afterwards that takes into account these NULL values. I cannot change it. Thanks, Liran

Liran Russo wrote: there's a script that runs afterwards that takes into account these NULL values. I cannot change it. Thanks, Liran Then I'm afraid we can no longer help you. You have a field that you are aggregating that contains NULLs. If this is expected behavior, then you can ignore the warning. There's nothing we can do in SSIS, because it's not an SSIS error -- it's a SQL error (warning, technically). If you're unwilling to change the way the query is written (by using ISNULL to convert NULLs to some known value) or the following script (which isn't likely doing anything because the NULLs have been stripped out of the aggregated result set), then you are at an impass.Phil

I'm having the same problem. Today for the first time in a long time a package failed for what seems to be due to this "warning." Now there were some source systems changes yesterday where maybe there are nulls where there never used to be any. The thing is if you run the query in SSMS it returns what I think are all the results anyway. And here's the more interesting thing, if I run the package manually it runs fine. It just fails when run as a job.There are maybe 40+ fileds in this query and I would hate to have to put ISNULL everywhere, and also why is a Warning causing an error in SSIS

I just threw SET ANSI_WARNINGS OFF in front of my source query and that did the trick

I'm seeing the same thing.I think this is an SSIS bug. Either it should always fail because of this warning, or it should never fail. I've entered this on Connect. Please vote for it as this has got to be fixed:https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=483175Now I'm left with going through every OLEDB Source in all my packages and finding the ones that do null aggregation. Fun stuff :-)http://artisconsulting.com/Blog/GregGalloway

The Microsoft support team is investigating this issue and we will update the thread once we have a proposed solution. It seems like an edge case where SSIS asks SQL Native Client for a certain amount of rows, and only in certain inconsistent edge conditions will the Ansi NULL warning be raised and fail the SSIS execution. Since its a warning, maybe it shouldn't fail the SSIS OLEDB source execution regardless of the intermittent nature of the warning from SQL Native Client provider. The workaround of SET ANSI_WARNINGS OFF should work, but its annoying to type that in each query that you use in SSIS sources. We are looking for a more robust solution. Thanks, JasonDidn't get enough help here? Submit a case with the Microsoft Customer Support team for deeper investigation - http://support.microsoft.com/select/default.aspx?target=assistance