MS SQL Server Bulk Loader
The MS SQL Server Bulk Loader writes rows into a Microsoft SQL Server table through the driver’s SQLServerBulkCopy API, which is typically much faster than inserting the same rows with a Table Output transform. Rows never leave the JVM as text: they are buffered and handed to the driver as typed values, so a NULL stays a NULL and no value can be mistaken for a field separator. Use the MSSQL Bulk Load action instead when the data is already sitting in a file the server itself can read. |
| This transform needs a connection of type MS SQL Server (Native). The bulk copy API belongs to the Microsoft JDBC driver, so a connection using another driver cannot be used here. |
The driver’s bulk copy cannot write into SQL Server 2025’s native json column type. mssql-jdbc 13.4.0 rejects such a destination column before any row is sent, reporting Data type geometry is not supported in bulk copy. Store the documents in nvarchar(max) to bulk load them, or use Table Output when the column has to be json. uniqueidentifier and xml columns bulk load normally; both are covered by the integration tests. |
Options
Settings
| Option | Description |
|---|---|
Transform name | Name of the transform. |
Connection | The MS SQL Server (Native) connection holding the target table. |
Target schema | The schema the target table lives in. Leave it empty to use the connection’s default schema. |
Target table | Name of the target table. |
Batch size | How many rows are buffered before they are sent to the server. Each batch is committed on its own, so this is both the memory bound and the commit granularity. Defaults to 100000. |
Truncate table | Truncate the target table before the first batch is loaded. |
Truncate only when there are rows | Leave the target table alone when the incoming stream turns out to be empty. Without this, an empty stream still truncates. |
Rows are passed on unchanged to the next transform, so the loader can sit in the middle of a pipeline.
Bulk copy options
These map one to one onto the driver’s SQLServerBulkCopyOptions.
| Option | Description |
|---|---|
Lock table | Take a bulk update lock on the table for the duration of the load. Faster, but no one else can load into the table at the same time. Enabled by default. |
Keep identity values | Load the identity values coming from the stream instead of letting SQL Server generate them. |
Keep NULL values | Write NULL for an empty value instead of falling back to the column default. |
Check constraints | Check the table constraints while the rows are loaded. Disabled by default, which is part of why bulk copy is fast. |
Fire triggers | Fire the insert triggers on the target table while the rows are loaded. Disabled by default. |
Allow encrypted value modifications | Needed to bulk load into columns protected by Always Encrypted. The values are not validated against the column encryption key. |
Bulk copy timeout (s) | How long one batch may take before it is abandoned. 0, the default, means no timeout. |
Fields
| Option | Description |
|---|---|
Specify database fields | Map stream fields onto table columns explicitly in the grid below. When this is disabled, every incoming field is loaded into the column with the same name and the grid is ignored. |
Table field | The column in the target table to write into. |
Stream field | The field in the incoming rows to read from. |
Order hint | Tell SQL Server that the incoming rows are already sorted on this column, ascending or descending. When the column backs the clustered index this lets the server skip a sort. Only set it when the rows really are sorted that way - the server trusts the hint. |
Use Get fields to fill the grid with the incoming fields mapped onto same-named columns, or Enter field mapping to draw the mapping between the incoming fields and the target table’s columns.
The SQL button generates the DDL that would make the target table able to accept what this transform writes.