Recently, while I was working on SSIS packages, I encountered an error “ A buffer failed while allocating .. bytes”. Entire package was working perfectly when the number of rows transferred were optimal. However, as soon as the package started loading millions of rows, there was a sudden error complaining about buffer.
When I went into the depth of the error, the error was due to the “UNION ALL” used in the Data Flow task. The error was “[-1073450974] SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Union All .." (541) failed with error code 0x8007000E while processing input "Union All Input " (615). 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. “
The package I was trying to execute was a Fact package, where in the Data Flow task, I have used various Lookup Transformations to fetch the Surrogate key from the Dimensions. This is a routine process in a DFT, where we try to get the Surrogate Key for Dimension and if its not available, we store it as an “Unknown” value. This require, Lookup Transformation, followed by a Derived column transformation and then finally Merging both the outputs via Union All transformation.
Now, here was the issue, basically, each column in a DFT is identified by a lineageid starting from the Source. When this column is passed through Union all, it changes the lineageid of the column and treats it as new column. Hence, every time a column is passed via Union All task, it will generate a new lineageid and a new buffer for it. This requires more space as compared to the column which is directly passed from source to destination. The aid for this issue is when a package is aimed to load huge volume of data, try to avoid use of Union All.
Instead of using Union All to merge, you can set the Lookup Transformation “ Specify how to handle rows with no matching entries” property to “IGNORE FAILURE”
Specifying this property, will always send every row as “Match Output”, with the rows with “no match entries with NULL”. Connect the Derived Column Transformation from the Lookup with “Match output”.
Inside the Derived Column Transformation, check for the Key value, if it is NULL , then take Unknown else the same key.
Please let me know your feedback on this problem. If anyone has encountered such error, let me the work around used to handle it.