When trying to use SSIS SQL Server Destination to improve data insertion performance sometimes buffer swapping occurs when RAM is strained out. This results in SSIS sometimes giving the below error:
Information: The buffer manager failed a memory allocation call for 8912512 bytes, but was unable to swap out any buffers to relieve memory pressure. 104744 buffers were considered and 104744 were locked. Either not enough memory is available to the pipeline because not enough are installed, other processes were using it, or too many buffers are locked.
[SS_DST_Fact[1412]] Error: The attempt to send a row to SQL Server failed with error code 0x80004005.
[OLE_SRC_[1]] Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.
This is due to the fact that by default the SQL Server Destination property MaxInsertCommitSize is set to 0 which leads to SSIS trying to commit all rows in one bulk insert, this can be very straining to SSIS if there are huge amounts of rows. to solve this issue set this number to X.
In my case setting it to 100 000 Rows did the trick.
Information: The buffer manager failed a memory allocation call for 8912512 bytes, but was unable to swap out any buffers to relieve memory pressure. 104744 buffers were considered and 104744 were locked. Either not enough memory is available to the pipeline because not enough are installed, other processes were using it, or too many buffers are locked.
[SS_DST_Fact[1412]] Error: The attempt to send a row to SQL Server failed with error code 0x80004005.
[OLE_SRC_[1]] Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.
This is due to the fact that by default the SQL Server Destination property MaxInsertCommitSize is set to 0 which leads to SSIS trying to commit all rows in one bulk insert, this can be very straining to SSIS if there are huge amounts of rows. to solve this issue set this number to X.
In my case setting it to 100 000 Rows did the trick.
No comments:
Post a Comment