SSIS ERROR: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020

Question

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?

Ouabain

There may be error messages posted before this with more information about the failure Is this a 2005/2008 or a 2012+ version of SSIS? – Fear

I have the same issue today. I use SSIS 2012. The code works in QA environment. So details suggest it is a data issue – Sempach

Answer 1

Well, I managed to work the issue with my packages. I was using 2012 SSIS version, but I executed packages in 32 bit environment in BIDS. The server acctually executed in 64 bit and for some projects that was the problem. One checkbox in step properties to make it execute in 32 bit env and I solved the problem we have been fighting for weeks.

Ouabain

Hi, checkbox where? – Postiche

Had the same question... I think I found it under properties of the Data flow Task, the ForceExecutionValueType field ? – Calvities

I'm sorry, I don't work there for over a year now, but it was property I believe in Job Scheduler on server. Every job was made of single steps, and these steps had different properties. – Ouabain

May be late, but perhaps it can help you or someone else: you can find the checkbox on a SSIS job step from the SQL Job Agent. On the job step stay on General, go to tab Configuration, go to subtab Advanced and you'll see a checkbox "32-bit runtime" on the bottom. This will make just this job step run in 32-bit (X86). – Circumgyration

Answer 2

I was also facing the same issue, I just did below step.

Open Data Flow tab>>Click anywhere except any task and then Right Click>>Properties>>Change **ForceExecutionValueType** to **Int64**

You can follow my screenshot:

Trixi

Answer 3

Watch out for indexes on the destination tables - especially unique ones because this will throw an error that's doesn't pinpoint the problem.

Seiden

Also watch out for non-nullable columns on the destination tables, this will throw an error that's doesn't pinpoint the problem. – Olid

Answer 4

For people who stumbled here for the same error. If you are trying to copy data from one Azure SQL database to another using SQL Server Import and Export Wizard. Use the 64-bit version.

From your windows search for SQL Server 2019 Import and Export Data (64-bit).

Approachable