none
Error code: 0x80004005.

    Question

  • Good Morning All,

    I have a package that has been running for months, its a nightly data load and scheduled via SSMS. Last night it failed. The Package has 8 sequence containers and i figured which container failed and i manually executed the container and got the following error messages.

    [OLE DB Source [1]] 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 Server Native Client 10.0"  Hresult: 0x00040EDA  Description: "Warning: Null value is eliminated by an aggregate or other SET operation.".

    [SSIS.Pipeline] Error: 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 the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.

    [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Union All" (1499) failed with error code 0xC0047020 while processing input "Union All Input 2" (1641). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.

    These errror messages are from the execution results. In SSMS when i clicked on the job history i have an error message:

    Message

    Executed as user: AD\SQLSERVICE. Microsoft (R) SQL Server Execute Package Utility  Version 10.50.2500.0 for 64-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    Started:  1:00:00 AM  Error: 2013-02-21 02:59:43.28     Code: 0xC0202009     Source: Import Vehicle Facts COALESCE 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 Server Native Client 10.0"  Hresult: 0x00040EDA  Description: "Warning: Null value is eliminated by an aggregate or other SET operation.".  End Error  Error: 2013-02-21 02:59:43.28     Code: 0xC0047038     Source: Import Vehicle Facts COALESCE SSIS.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 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).  Started:  1:00:00 AM  Finished: 3:51:42 AM  Elapsed:  10301.9 seconds.  The package execution failed.  The step failed.

    The data is loaded using a TSQL Query, can anyone please guide me as what to look into the TSQL Query? Any advice or direction is greatly appreciated.

    Thanks

    Thursday, February 21, 2013 3:08 PM

Answers

All replies

  • I guess the first thing that I would do is to just run the query in SSMS and see if it returns successfully.   I'm not seeing anything in the error messages that points to the actual issue.

    Chuck Pedretti | Magenic – North Region | magenic.com

    Thursday, February 21, 2013 3:14 PM
  • Thanks Chuck,

    I executed the TSQL in SSMS and it ran fine with no issues. Do you think this error might be misleading?

    Thursday, February 21, 2013 3:18 PM
  • Thanks Chuck,

    I executed the TSQL in SSMS and it ran fine with no issues. Do you think this error might be misleading?


    Could be.  DO you have the ability to run the SSIS package interactively?

    Chuck Pedretti | Magenic – North Region | magenic.com

    Thursday, February 21, 2013 3:27 PM
  • Thanks Chuck you mean use CmdExec or using BIDS? I executed from BIDS manually and it failed.
    Thursday, February 21, 2013 3:32 PM
  • Thanks Chuck you mean use CmdExec or using BIDS? I executed from BIDS manually and it failed.

    I mean using BIDS.  Did it give you any additional indications of what the failure was?  Knowing which block it failed on can sometimes help you track it down.  A data viewer may help as well.

    Chuck Pedretti | Magenic – North Region | magenic.com

    Thursday, February 21, 2013 3:38 PM
  • Chuck in my original that i posted the first 3 messages are from BIDS and the last error message that i posted is from SSMS.

    Thanks

    • Marked as answer by thinkingeye Thursday, February 21, 2013 5:48 PM
    • Unmarked as answer by thinkingeye Thursday, February 21, 2013 5:48 PM
    Thursday, February 21, 2013 3:42 PM
  • I solved it by adding SET ANSI WARNING OFF and the package worked fine.
    • Marked as answer by thinkingeye Thursday, February 21, 2013 5:48 PM
    Thursday, February 21, 2013 5:48 PM