SQL Loader Bulk Load - support bcp's -b batch_size argument

SnapLogic uses bcp in the SQL Server Bulk Load snap; however it does not make use of the -b batch_size argument. As a result, all rows are imported as one batch. We were attempting to load 582 million rows (temp file was 426 GiB in size) and bcp failed because the database’s transaction log ran out of space. Adding support for bcp’s -b batch_size argument would allow for better database resource utilization and would also yield better performance of the bulk load.

We have created an enhancement request to allow the batch size to be configurable in the snap. A workaround you could try is to create a /tmp/mybcp.sh script on the Groundplex nodes with the contents

/<REAL_BCP_PATH>/bcp "$@" -b 1000000

Change it to be executable

chmod +x /tmp/mybcp.sh

And use /tmp/mybcp.sh as the BCP absolute path in the snap. The steps would be similar on Windows. The argument encoding (for double quotes) would be the issue to look out for with this workaround.

1 Like

Thank you. This worked; I just needed to swap the order of the arguments.

/<REAL_BCP_PATH>/bcp “$@” -b 1000000

Thanks for the note, I have updated the steps above