Microsoft SQL Server Batch

Microsoft SQL Server Batch
Certified
Bulk insert rows into Microsoft SQL Server using prepared statements

Reads ION-formatted data from Kestra internal storage and performs high-performance batch inserts using JDBC batch operations. Data is processed in chunks (default 1,000 rows) to optimize memory and performance. Supports auto-commit for databases without transaction support.

yaml
type: io.kestra.plugin.jdbc.sqlserver.Batch

Examples

Fetch rows from a table and bulk insert to another one.

yaml
id: sqlserver_batch_query
namespace: company.team

tasks:
  - id: query
    type: io.kestra.plugin.jdbc.sqlserver.Query
    url: jdbc:sqlserver://dev:41433;trustServerCertificate=true
    username: "{{ secret('SQL_USERNAME') }}"
    password: "{{ secret('SQL_PASSWORD') }}"
    sql: |
      SELECT TOP (1500) *
      FROM xref;
    fetchType: STORE

  - id: update
    type: io.kestra.plugin.jdbc.sqlserver.Batch
    from: "{{ outputs.query.uri }}"
    url: jdbc:sqlserver://prod:41433;trustServerCertificate=true
    username: "{{ secret('SQL_USERNAME') }}"
    password: "{{ secret('SQL_PASSWORD') }}"
    sql: |
      insert into xref values( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )

Fetch rows from a table and bulk insert to another one, without using sql query.

yaml
  id: sqlserver_batch_query
  namespace: company.team

  tasks:
    - id: query
      type: io.kestra.plugin.jdbc.sqlserver.Query
      url: jdbc:sqlserver://dev:41433;trustServerCertificate=true
      username: sql_server_user
      password: "{{ secret('SQL_SERVER_PASSWD') }}"
      sql: |
        SELECT TOP (1500) *
        FROM xref;
      fetchType: STORE

    - id: update
      type: io.kestra.plugin.jdbc.sqlserver.Batch
      from: "{{ outputs.query.uri }}"
      url: jdbc:sqlserver://prod:41433;trustServerCertificate=true
      username: "{{ secret('SQL_USERNAME') }}"
      password: "{{ secret('SQL_PASSWORD') }}"
      table: xref

Properties

from *string

Input file from internal storage

URI of the source file (kestra://) containing rows to insert

Pebble expression referencing an Internal Storage URI e.g. {{ outputs.mytask.uri }}.

url *string

The JDBC URL to connect to the database

chunk integerstring

Default1000

Batch size per executeBatch call

Number of rows sent per JDBC batch before commit; default 1,000

columns array

SubTypestring

Columns bound to placeholders

Ordered column names matching ? placeholders; if omitted, placeholder count must match all columns in the input row

connectionPoolSize integerstring

Default10

Maximum number of pooled connections

Maximum connections held in the pool for a given URL and credentials. Default 10. Increase for flows that run many concurrent queries against the same database to avoid waiting for an available connection. Ignored when connectionPooling is false or for embedded drivers.

connectionPooling booleanstring

Defaulttrue

Reuse database connections via a connection pool

When true (default), connections are pooled and reused across executions, keyed by URL and credentials, removing the connect and TLS-handshake cost on each run. Set to false if your SQL relies on session state persisting on the connection (for example SET search_path, session-scoped temp tables or variables), since pooled connections are reused. Embedded drivers (DuckDB, SQLite, MS Access) never pool regardless of this setting.

encrypt string

DefaultFALSE

Possible Values

TRUEFALSESTRICTOPTIONAL

Whether to encrypt the connection

Controls JDBC encryption between the client and SQL Server. Defaults to FALSE to preserve backward compatibility with mssql-jdbc 12.x behavior. Set to TRUE or STRICT for encrypted connections. Note: if your SQL Server has "Force Encryption" enabled server-side, the server will require TLS regardless of this setting. In that case, ensure your server supports TLS 1.2+ and its certificate is properly configured, otherwise you may get "unexpected_message" errors during the TLS handshake.

hostNameInCertificate string

The hostname expected in the server certificate

Specifies the host name to be used when validating the SQL Server TLS/SSL certificate. If not set, the driver uses the server name from the connection URL.

inputHandling string

DefaultAUTO

Possible Values

AUTOSTREAMLOCAL

Input handling strategy

Controls how input is read during processing and retries. AUTO buffers small files locally (<= localBufferMaxBytes) and streams large files. STREAM always streams from internal storage. LOCAL always buffers input to a local temporary file before processing.

localBufferMaxBytes integerstring

Default104857600

Maximum number of bytes buffered locally

Used by AUTO and LOCAL input handling. In AUTO, files larger than this threshold are streamed. In LOCAL, files larger than this threshold fail fast.

maxRetries integerstring

Default3

Maximum number of retries for transient failures

Retries are attempted only for transient failures such as temporary I/O and recoverable SQL errors.

password string

The database user's password

pluginDefaultsRef string

Reference (ref) of the pluginDefaults to apply to this task.

resumeOnRetry booleanstring

Defaulttrue

Resume from the last successfully committed chunk on retry

retryBackoff string

DefaultPT1S

Delay between retry attempts

Uses ISO-8601 duration format, for example PT1S.

retryScope string

DefaultINPUT

Possible Values

NONEINPUTALL

Controls which failures are retried

INPUT retries input handling failures, ALL retries all retryable failures.

sql string

Parameterized INSERT statement to execute

Prepared INSERT with ? placeholders for each bound column. Example: INSERT INTO VALUES (?, ?, ?) for three columns; use column list if inserting a subset

table string

Table used to auto-discover columns

Retrieves column names from the given table when columns is empty. If sql is also omitted, an INSERT statement is generated automatically using the discovered columns

timeZoneId string

The time zone id to use for date/time manipulation. Default value is the worker's default time zone id

trustServerCertificate booleanstring

Defaultfalse

Whether to trust the server certificate without validation

When set to true, the driver does not validate the SQL Server TLS/SSL certificate. Useful for development or self-signed certificates.

trustStore string

The path to the trust store file

Specifies the path (including file name) to the certificate trust store file. Used when encrypt is TRUE or STRICT to validate the server certificate.

trustStorePassword string

The trust store password

The password used to access the trust store file.

username string

The database user

Outputs

rowCount integer

Total rows read

updatedCount integer

Rows inserted or updated

Metrics

query counter

Unitqueries

The number of batch queries executed.

records counter

Unitrecords

The number of records processed.

updated counter

Unitrecords

The number of records updated.

Liquibase

Tasks that run Liquibase change management commands.

Data

PostgreSQL

Tasks that connect to PostgreSQL via JDBC to run queries and loads.

Data

Microsoft SQL Server BatchCertifiedBulk insert rows into Microsoft SQL Server using prepared statements

Microsoft SQL Server Batch

More Plugins in this Category

Liquibase

PostgreSQL

Microsoft SQL Server Batch
Certified
Bulk insert rows into Microsoft SQL Server using prepared statements