SSIS: The component returned a failure code when the pipeline engine called PrimeOutput()

Recently, while working on SSIS package, we encountered the error after few minutes of execution of package. To get rid of this issue, we implemented one workaround:

Go to Data Flow –> Properties

Decrease the default buffer size to 1MB i.e. 1048576 Bytes

Decrease the default Max Buffer rows to 1000 as shown below:

After implementing this workaround, we didn’t get this error anymore.

However, to fix this error, we need to see how we can optimize the performance of the package as this specific error message comes up when any memory extensive operation is done. In our case, there were many sort blocks which was causing this issue as Sort blocks sort the data after loading the entire result set into memory and is also slower than sorting the result set at source(SQL Query itself). Please refer to one of my previous blog : SSIS: The column with the sortkeyposition value of 1 is not valid. It should be 0 on how we can remove sort blocks from SSIS package by sorting the data at source level to reduce the load on memory and thereby fixing this issue.

Hope it helps !!