SQL Server Bulk Loader

[benwh56]

Hello all,

I'm currently attempting to write a large table (roughly 15mil rows and 30 some-odd columns) to Azure Synapse from a .yxdb. Initially i was trying with a regular old ODBC connection in the Output tool, but this was fairly slow. I also noticed that I would get a warning saying, "For faster write performance, it is recommended that the Bulk Load Connection be used instead of the ODBC Connection." 

I'm currently using SQL Service Bulk Loader for file format, but it appears to be even slower. 

I'm pretty novice at this so I'm not really sure where I should start in terms of tweaking things to make the write operation perform faster.

A couple of facts:

- I'm running this from Designer 2024.1.1

- I'm still getting the "For faster write performance, it is recommended that the Bulk Load Connection be used instead of the ODBC Connection." warning. Perhaps I'm not actually using the Bulk Loader? (All I did was change file format as denoted on option 2 in the screenshot below).

- My transaction size is 5000.

- I'm attempting this while WFH and using a VPN. I think this may be part of the problem and if I were to attempt this from the office it may run a bit quicker. Not sure if that's wishful thinking or not... 

- Below is my output tool's configuration - should be all default settings.

 

I'd be grateful for any tips/tricks/suggestions/corrections.

Thanks all!