Null value is eliminated by an aggregate or other SET operation.
Hi All i have a nightly jon that runs every night with no problems for a year, but recently the job has been failing with the following message: "Message Executed as user: IYAD\MOSS_BI. ...9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 2:40:01 AM Error: 2012-05-25 03:50:28.69 Code: 0xC0202009 Source: Import LETTER Facts OLE DB Source [1] Description: 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.". End Error Error: 2012-05-25 03:50:28.71 Code: 0xC0047038 Source: Import LETTER Facts DTS.Pipeline Description: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and t... The package execution fa... The step failed." I looked up and found out its an SSIS bug for 2005 and the work around is to set the ANSI OFF at the beginning of each query. Unfortunatley the job that i run has many packages and its very difficult to edit each sql query and add the ANSI off functionality in the beginning of each query, is there any one single place where i can add this functionality? Please need help. Thanks
May 25th, 2012 9:20am

You can set ANSI_WARNINGS off at the database level. That is actually the default on SQL 2008 and 2012 USE [master] GO ALTER DATABASE [dbName] SET ANSI_WARNINGS OFF WITH NO_WAIT GO Chuck Pedretti | Magenic North Region | magenic.com
Free Windows Admin Tool Kit Click here and download it now
May 25th, 2012 9:25am

Thanks Chiuck will try this and get back with you tomorrow once the Job Runs.
May 25th, 2012 9:42am

Chuck Do i need to set the ANSI_WARNINGS OFF on the source database or the STAGING ( Target Database) ? as the cource database has already has the "ANSI Warnings Enabled set to FALSE". Thanks
Free Windows Admin Tool Kit Click here and download it now
May 25th, 2012 10:06am

Should be the source database if that is where you are performing the aggregates. There is a connect issue that sounds like your problem. Makes me wonder if SQL 2005 is ignoring the DB level setting http://connect.microsoft.com/SQLServer/feedback/details/483175/failure-because-of-warning-about-null-aggregation#detailsChuck Pedretti | Magenic North Region | magenic.com
May 25th, 2012 10:10am

Thanks Chuck but i already went through the link before i posted it on this forum, but there is no solid solution :-( andi'm doing all my aggregates on the staging database. I mean in SSIS packages in the OLEDB Source i have a query that brings in the data (which aggregates the datat in some cases), so should i set the ANSI warnings off on the source? Thanks
Free Windows Admin Tool Kit Click here and download it now
May 25th, 2012 10:17am

If you are doing the aggregation queries on Staging then you need ANSI_WARNINGS OFF on that database. If you are doing them on the source then you need it off there. Not sure if that'll help or not since it appears that you will have to SET ANSI_WARNINGS OFF in each of your queries due to the bug documented in that connect item. But it is worth a try. Chuck Pedretti | Magenic North Region | magenic.com
May 25th, 2012 10:29am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics