'Import and Export Data' - Errors when creating excel file

SSIS and Import/Export
1

Morning all,

I was struggling last night with a query to get some data out of an old database. With the help of forum members the query now works perfectly.

I was hoping the export would be straight forward but I keep getting errors that I just don't know how to solve. Error message generated by SQL Server 2008 R2 Import and Export Data :

- Copying to `Query` (Error)
Messages
Error 0xc0202009: Data Flow Task 1: 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.".
 (SQL Server Import and Export Wizard)
 
Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on component "Source - Query" (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.
 (SQL Server Import and Export Wizard)

Problem is I don't understand what the error message is trying to tell me, any ideas how I can solve this, all I want to do is get a few thousand records out of SQL...

Thanks

Dave

2

I'm assuming that the first error (well "warning") message is causing the second - and the second one is fatal.

The first one is only a warning. "Warning: Null value is eliminated by an aggregate or other SET operation." is generated where you have used an aggregate function like SUM(MyColumn) and on one/many rows the value of MyColumn is NULL. You can fix this with

SUM(COALESCE(MyColumn, 0))

(use 0.0 is MyColumn is a FLOAT, and some suitable string value, a '' blank string even, if it is a VARCHAR and you are using MIN/MAX etc.)

If you cannot fix it for some reason there is an ANSI WARNINGS setting somewhere that you might be able to twiddle with, but although I have used those in the past I now avoid them like the plague as whenever I did use them they caused all hell to break out somewhere else in my code!