April 16, 2010 at 12:39 pm
Hello,
I've searched the thread but have not been able to find any similar issue.
I have exporting data to a txt file or a CSV file. When I run this export from Microsoft Visual Studio (2005) (right click on the package and execute it), it runs fine. But when I run it using execute package utility, it fails . In the error message the following warning is included - "Null Value is eliminated by an aggregate or Other SET operation" . The rest of the error doesn't make any sense to me "OLEDB Error has occured Error code 0x80004005".
I am wondering if I need to suppress this warning? I don't want to use ISNULL as of now because there are atleast 50 such statements which can give NULLS.
ANd I don't want to execute it from Visual Studio, because in the application it is supposed to be called from a job.
Please help.
Thanks
April 16, 2010 at 2:40 pm
It may help if you posted the entire error message not just parts of it.
April 16, 2010 at 3:52 pm
Here are the error codes:
Error: SSIS Errorcode DTS_E_OLEDBERROR. An OLEDB Error has occured. Error Code 0x80004005. An OLEDB record is available.Source Microsoft SQL Native Client.Hresult 0x000. Description "Warning a Null value is eliminated by an aggregate or other SET operation>
Error: SSIS Errorcode DTS_E_Primeoutput failed. The PrimeOutput method on component "OLEDB Source" (41734) returned error code 0xc0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the errorcode is defined by the component, but the error is fatal and pipeline stopped executing.
Thanks
Ayush
April 17, 2010 at 12:15 pm
Thanks Phil. I tried doing the SET ANSI_NULLS OFF, but it didn't work. I read about it, and in my query I am not doing any comparison where a null is involved.
I am using pivot tables in my query though.
Also, I dont understand why it runs fine when I execute from Visual Studio and fails when execute by double clicking (execute package utility).
Thanks
April 19, 2010 at 12:44 am
Two observations:
1. The package runs successfully when I use max(isnull[columne_name],0) for all the max columns. These max columns are maxed in the select part using the columns returned by the pivot table.
2. Strangely but surely, if I give any condition which eliminates even a single row from the output , the package runs fine, without any changes in the original query. Example:
I used "select a,b,c........ from tbl1 join tbl2.... where accountnbr not in (123456). Here 123456 is one valid account which appears in the output file. The package runs successfully if I eliminate any single account from the generating output file.
Any help will be appreciated.
Here is the complete Error log:
OnPipelinePrePrimeOutput,GTCS030BBI,GTCAcct\bbiadmin,Export Players,{c0f805bb-c86c-4738-9b8c-26de6207025a},{8E17090C-EEF1-48FA-85E3-50370089B2AD},4/17/2010 10:58:54
PM,4/17/2010 10:58:54 PM,0,0x,PrimeOutput will be called on a component. : 41734 : OLE DB Source
OnPipelineRowsSent,GTCS030BBI,GTCAcct\bbiadmin,Export Players,{c0f805bb-c86c-4738-9b8c-26de6207025a},{8E17090C-EEF1-48FA-85E3-50370089B2AD},4/17/2010 10:58:54
PM,4/17/2010 10:58:54 PM,0,0x,Rows were provided to a data flow component as input. : : 42886 : OLE DB Source Output : 42430 : Derived Column 1 : 42431 : Derived Column Input :
914
OnPipelineRowsSent,GTCS030BBI,GTCAcct\bbiadmin,Export Players,{c0f805bb-c86c-4738-9b8c-26de6207025a},{8E17090C-EEF1-48FA-85E3-50370089B2AD},4/17/2010 10:58:55
PM,4/17/2010 10:58:55 PM,0,0x,Rows were provided to a data flow component as input. : : 46408 : Derived Column Output : 46402 : Derived Column : 46403 : Derived Column Input :
914
OnPipelineRowsSent,GTCS030BBI,GTCAcct\bbiadmin,Export Players,{c0f805bb-c86c-4738-9b8c-26de6207025a},{8E17090C-EEF1-48FA-85E3-50370089B2AD},4/17/2010 10:58:55
PM,4/17/2010 10:58:55 PM,0,0x,Rows were provided to a data flow component as input. : : 47831 : Derived Column Output : 47826 : Flat File Destination : 47827 : Flat File
Destination Input : 914
OnPipelineRowsSent,GTCS030BBI,GTCAcct\bbiadmin,Export Players,{c0f805bb-c86c-4738-9b8c-26de6207025a},{8E17090C-EEF1-48FA-85E3-50370089B2AD},4/17/2010 10:58:55
PM,4/17/2010 10:58:55 PM,0,0x,Rows were provided to a data flow component as input. : : 42886 : OLE DB Source Output : 42430 : Derived Column 1 : 42431 : Derived Column Input :
914
OnPipelineRowsSent,GTCS030BBI,GTCAcct\bbiadmin,Export Players,{c0f805bb-c86c-4738-9b8c-26de6207025a},{8E17090C-EEF1-48FA-85E3-50370089B2AD},4/17/2010 10:58:55
PM,4/17/2010 10:58:55 PM,0,0x,Rows were provided to a data flow component as input. : : 46408 : Derived Column Output : 46402 : Derived Column : 46403 : Derived Column Input :
914
OnPipelineRowsSent,GTCS030BBI,GTCAcct\bbiadmin,Export Players,{c0f805bb-c86c-4738-9b8c-26de6207025a},{8E17090C-EEF1-48FA-85E3-50370089B2AD},4/17/2010 10:58:55
PM,4/17/2010 10:58:55 PM,0,0x,Rows were provided to a data flow component as input. : : 47831 : Derived Column Output : 47826 : Flat File Destination : 47827 : Flat File
Destination Input : 914
OnError,GTCS030BBI,GTCAcct\bbiadmin,Export Players,{c0f805bb-c86c-4738-9b8c-26de6207025a},{8E17090C-EEF1-48FA-85E3-50370089B2AD},4/17/2010 10:58:55 PM,4/17/2010 10:58:55
PM,-1071636471,0x,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.".
OnError,GTCS030BBI,GTCAcct\bbiadmin,Export Players,{c0f805bb-c86c-4738-9b8c-26de6207025a},{8E17090C-EEF1-48FA-85E3-50370089B2AD},4/17/2010 10:58:55 PM,4/17/2010 10:58:55
PM,-1073450952,0x,SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "OLE DB Source" (41734) 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.
OnPipelinePostPrimeOutput,GTCS030BBI,GTCAcct\bbiadmin,Export Players,{c0f805bb-c86c-4738-9b8c-26de6207025a},{8E17090C-EEF1-48FA-85E3-50370089B2AD},4/17/2010 10:58:55
PM,4/17/2010 10:58:55 PM,0,0x,A component has returned from its PrimeOutput call. : 41734 : OLE DB Source
OnError,GTCS030BBI,GTCAcct\bbiadmin,Export Players,{c0f805bb-c86c-4738-9b8c-26de6207025a},{8E17090C-EEF1-48FA-85E3-50370089B2AD},4/17/2010 10:58:55 PM,4/17/2010 10:58:55
PM,-1073450975,0x,SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more
information on why the thread has exited.
OnError,GTCS030BBI,GTCAcct\bbiadmin,Export Players,{c0f805bb-c86c-4738-9b8c-26de6207025a},{8E17090C-EEF1-48FA-85E3-50370089B2AD},4/17/2010 10:58:55 PM,4/17/2010 10:58:55
PM,-1073450951,0x,SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in
another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled.
OnError,GTCS030BBI,GTCAcct\bbiadmin,Export Players,{c0f805bb-c86c-4738-9b8c-26de6207025a},{8E17090C-EEF1-48FA-85E3-50370089B2AD},4/17/2010 10:58:55 PM,4/17/2010 10:58:55
PM,-1073450975,0x,SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0047039. There may be error messages posted before this with more
information on why the thread has exited.
April 19, 2010 at 2:53 am
ct_man12 (4/17/2010)
Thanks Phil. I tried doing the SET ANSI_NULLS OFF, but it didn't work. I read about it, and in my query I am not doing any comparison where a null is involved.I am using pivot tables in my query though.
Also, I dont understand why it runs fine when I execute from Visual Studio and fails when execute by double clicking (execute package utility).
Thanks
I believe that the error occurs because you are aggregating over a column which contains one or more NULLs in its data - regardless of any comparison that you are making in your query.
The fact that you were able to resolve the error using IsNull() further backs this up.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply