SSIS ERROR: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020
I have problems with SSIS process(acctually the same problem occurs for two different processes). So, we are doing some ETL work using SSIS. We have Business Intelligence project that executes without errors from Visual Studio. However when its deployed on the server and scheduled as a job it fails with errors like: *INTRASTAT_Towar:Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Union All 3" (876) failed with error code 0xC0047020 while processing input "Union All Input 2" (916). 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.* *INTRASTAT_Towar:Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on istat_dekl_wynik_poz SELECT returned error code 0xC02020C4. 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. *INTRASTAT_Towar:Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.* The other returned errors are similar, sometimes instead of 0xC0047020, there is unspecified error. Errors occur only on this one table, that has a lot of different stuff inside data flow task(unions, multicasts, conditional split). Most other data flows have just source, dest and transformation, and they are not causing any problems. I've been suggested to try manipulating DefaultBufferMaxRows and DefaultBufferSize properties values for the data flow task, but after doing some research I dont belive that it will solve the issue, as they are currently put on default values. Any suggestions?
Hi Lukjak Its very hard to tell the root cause of the error when you get a generalized statement while package fails during sql job run. I too had to dig around a lot last night (working on a issue in my project) to try to understand why ssis package fails via job but succeeds in BIDS. After a long struggle I was able to replicate the issue using BIDS and the solution was the package had got corrupted. So my advice would be first try to replicate the issue using BIDS by may be running the package on server and see how it goes. As per your error - I think you are right that changing those properties would benefit. BEST OF LUCK
Just to close the topic - I solved the issue. In my case it was the 32/64 bit problem. When I run package from BIDS it executed in 32 bit environiment, and when deployed and run from server it executed in 64 bit environiment. Checking one checkbox fixed error we been fighting for weeks.