PrimeOutputFailed on last buffer?
I'm running SSIS 2008 SP1 (10.0.1600 shows in SSMS when I connect to SSIS).The following is the error message I'm getting in the SQL agent job which calls dtexec. It has run consistently for months and just failed twice in 6 hours but is not currently failing.Error: 2009-08-08 06:00:52.02 Code: 0xC0202009 Source: DF (My Data Flow) OLESRC (My 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 Server Native Client 10.0" Hresult: 0x00040EDA Description: "Warning: Null value is eliminated by an aggregate or other SET operation.". End Error Error: 2009-08-08 06:00:52.09 Code: 0xC0047038 Source: DF (My Data Flow) SSIS.Pipeline Description: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "OLESRC (My 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 the pipeline stopped executing. There may be error messages posted before this with more information about the failure. End Error DTExec: The package execution returned DTSER_FAILURE (1).This error occurs after the next to last buffer to flow from the source in this data flow. That makes me suspicious. Let me describe the data flow.OLEDB Source connected to a Row Count transform connected to an OLEDB Destination. The OLEDB Source is in SQL command mode and runs a sql command which does a group by and a min on a nullable string column. (Thus the "null value is eliminated" warning. Indicentally, it does eliminate null values every time the package runs, even when it succeeds, so I think that warning message is just a warning.)When I look at the OnPipelineRowsSent events for the path leaving the OLEDB Source...Yesterday when it succeeded at noon, it pumped 269,168 rows which was 27 buffers of 9,947 rows and one buffer of 599 rows and one buffer of zero rows.At midnight last night it pumped 27 buffers of 9,947 then failed with the above error. Same when it failed at 6am today.Today when it succeeded, it pumped 27 buffers of 9,947 rowsand one buffer of 820 rowsand one buffer of zero rows.The source system is a SQL2005 server on the LAN. I don't suspect it's because of a dropped network connection because it happened at midnight and 6am... and because multiple SSIS packages are executing in parallel pulling from this SQL server, and this is the only one which failed.Anyone run into this before or have any suggestions?http://artisconsulting.com/Blog/GregGalloway
August 8th, 2009 7:15pm

The first error you're getting isn't the error in PrimeOutput, it's an OLE DB error returned by the provider: "Warning: Null value is eliminated by an aggregate or other SET operation.". Although the text returned by the OLE DB provider says it's a warning, it appears to be returning a fatal error code or signal to SSIS.In the instances where this package runs successfully, do you also see this "warning" returned by the OLE DB provider?Can you alter the SQL statement used in the source to eliminate the NULL warning? If this warning is causing the OLE DB provider to send a message that SSIS is interpreting as a fatal error (sometimes oralways) then we should try to eliminate that condition. If the root cause is actually something else, and this warning issued by the OLE DB provider is just covering it up, then eliminating the warning may uncover the true problem.BTW - if the connection was dropped, I would expect a different message - but then, who knows?
Free Windows Admin Tool Kit Click here and download it now
August 8th, 2009 9:48pm

Thanks for the reply, Todd.The SQL query when run in SSMS returns the warning every time, even when the SSIS package succeeds. My suspicion is that something odd is going on, and some error happens, and PrimeOutput only has that warning available (or the warning is masking/hiding a real error). I don't think that warning is the culprit, but I'm glad to be proved wrong.I might be able to stick "SET ANSI_WARNINGS OFF" at the top of the SQL query to hide the warning. But the problem is that I can't consistently repro the error (not knowing what the error is) so I'm not sure if adding that to the top will prove/help anything.Honestly, if that "null value is eliminated" warning is causing problems, lots of my packages (and other people's packages) would be blowing up.Thoughts?http://artisconsulting.com/Blog/GregGalloway
August 8th, 2009 10:21pm

I'm not disagreeing with you - just wanting to eliminate the possibility that that warning is "covering up" the real fatal cause of the error. I also don't think that warning is at all at fault.Another thought is to have SQL Profiler run (continuously) to monitor activity between SSIS and your database. Perhaps it will show some kind of disconnection problem, deadlock, whatever may be causing the issue... ?
Free Windows Admin Tool Kit Click here and download it now
August 8th, 2009 11:05pm

Hi Greg,The final buffer of zero rows is normal. The pipeline engine sends through a final, empty buffer after all the data has been processed. In 2005, this was required because of the way the pipeline engine worked. In 2008, it's not necessary any longer, but it was kept for backwards compatability - too many scripts relying on an empty buffer to identify the end of rowset condition.Anyway, what that means is that it's truncating your last real buffer.That makes me think the query isreturning dirty data. Is it possible that the data is changing while the query is running? Are you using a NOLOCK hint? This sounds vaguely like it might be related to a buffer race condition bug that the team identified recently. However, the symptom for that bug is a buffer of empty rows being sent thru the pipeline, so it's usually the destination that errors out, not the source.John Welch | www.mariner-usa.com | www.agilebi.com | ssisUnit.codeplex.com
August 9th, 2009 6:58pm

Todd, you may be right. I added in "SET ANSI_WARNINGS OFF" in case it blows up again so that possibly I'll get a different/better error message.John, thanks for the note. Are you suggesting I should or shouldn't use NOLOCK. Currently, the query is not using NOLOCK.It does sound like an SSIS bug that might be something like a race condition. I have my doubts it's anything like the source system changing because it seems like it probably wouldn't have happened at midnight and 6am if that were the case. But I'll check with the gurus on the source system about when those tables change.http://artisconsulting.com/Blog/GregGalloway
Free Windows Admin Tool Kit Click here and download it now
August 9th, 2009 7:30pm

I'd suggest not using NOLOCK - that can cause dirty reads. Since you aren't using it, I'd guess it's unlikely that it's a source system issue. I'll bubble this up to the SSIS team, they may have some additional ideas.John Welch | www.mariner-usa.com | www.agilebi.com | ssisUnit.codeplex.com
August 9th, 2009 7:39pm

There is a fix in next CU fora problem that only happens under memory pressure and the error message can vary (but this one doesn't look common). As the CU won't be available in a few weeks, setup a perfmon counter log (include just the memory and process objects will be enough) with an interval of 5 seconds (on the machine you run SSIS packages) will be some quick things you may further look at for now.Thanks.Liu An - MSFT
Free Windows Admin Tool Kit Click here and download it now
August 10th, 2009 8:52pm

Liu-Thanks for the reply. I do log PerfMon counters and I don't believe it's a memory pressure issue. It's a 64-bit box and 64-bit dtexec, and the box never got below 5GB available memory. The ETL doesn't usually consume above 2GB at its peak memory usage.When you mentioned to log perfmon counters of "just the memory and process objects" which particular counters did you mean? I wasn't entirely clear. Did you just mean all the perfmon counters under the Memory category, and all the perfmon counters for the dtexec process under the Process category?Any other thoughts?http://artisconsulting.com/Blog/GregGalloway
August 11th, 2009 1:01am

Have you tried the ADO.Net source?
Free Windows Admin Tool Kit Click here and download it now
August 11th, 2009 1:08am

I haven't tried other sources/providers/drivers. The problem is that I can't reproduce the error not knowing what it is. If I'm able to, I'll try that in addition to other providers. Good suggestion.http://artisconsulting.com/Blog/GregGalloway
August 11th, 2009 1:33am

putting SET ANSI_WARNINGS OFF at the top of the SQL query works around the problem.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
Free Windows Admin Tool Kit Click here and download it now
August 16th, 2009 6:32am

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
March 24th, 2011 12:25am

Hi guys we got same error from a package that run nightly ok for two month (ssis package which is stored in MSDB executed via JOB on 64 bit server 2003 / sql server 2008). since package run ok with numerous null records we suspected that error was caused when some kind of warning count threshold was reached data source queries are relativelly simple with a MAX / group by with many rows containing nulls in MAXed field Same package run OK via DTSExec and from dot net studio As a work around we added SET ANSI_WARNINGS OFF at start of SQL code in ssis package, migrated to server and it run OK. having these warning is bad coding practice so we will be recoding to remove null warning something like (max (isnull xxx, 0)) Small problem is that existing sql code is parameterised and adding SET ANSI_WARNINGS OFF to parameterised queries causes systanx error so for time being I had to also remove parameters from SQL. Is there a work around to be able to add SET ANSI_WARNINGS OFF to parameterised queries? thanks Peter
Free Windows Admin Tool Kit Click here and download it now
October 15th, 2011 7:46am

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

Other recent topics Other recent topics