Microsoft Access Batch
CertifiedBulk insert rows into a Microsoft Access database using prepared statements
Microsoft Access Batch
Bulk insert rows into a Microsoft Access database using prepared statements
Reads ION-formatted data from Kestra internal storage and performs high-performance batch inserts into a Microsoft Access database via the UCanAccess JDBC driver. Data is processed in chunks (default 1,000 rows) to optimize memory and performance.
type: io.kestra.plugin.jdbc.access.BatchExamples
Fetch rows from a query and bulk insert them into an Access table.
id: access_batch
namespace: company.team
tasks:
- id: query
type: io.kestra.plugin.jdbc.access.Query
url: jdbc:ucanaccess:///myfile.accdb
outputDbFile: true
sql: |
SELECT product_id, product_name, price
FROM products
LIMIT 1500
fetchType: STORE
- id: insert
type: io.kestra.plugin.jdbc.access.Batch
from: "{{ outputs.query.uri }}"
accessFile: "{{ outputs.query.databaseUri }}"
sql: INSERT INTO products_copy VALUES (?, ?, ?)
Properties
from *Requiredstring
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 *Requiredstring
The JDBC URL to connect to the database
accessFile string
Access database file (optional)
Optional URI to an existing Access database file stored in Kestra internal storage.
When provided, the file is downloaded into the task working directory and used as the Access database for the batch insert.
chunk integerstring
1000Batch size per executeBatch call
Number of rows sent per JDBC batch before commit; default 1,000
columns array
Columns bound to placeholders
Ordered column names matching ? placeholders; if omitted, placeholder count must match all columns in the input row
connectionPoolSize integerstring
10Maximum 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
trueReuse 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.
inputHandling string
AUTOAUTOSTREAMLOCALInput 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
104857600Maximum 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
3Maximum number of retries for transient failures
Retries are attempted only for transient failures such as temporary I/O and recoverable SQL errors.
newDatabaseVersion string
V2003V2000V2003V2007V2010V2016Access database version to use when creating a new database file
Specifies the Microsoft Access file format used when UCanAccess creates a new database. Only applies when the target database file does not already exist. See https://spannm.github.io/ucanaccess/20-getting-started.html for details.
password string
The database user's password
pluginDefaultsRef Non-dynamicstring
Reference (ref) of the pluginDefaults to apply to this task.
resumeOnRetry booleanstring
trueResume from the last successfully committed chunk on retry
retryBackoff string
PT1SDelay between retry attempts
Uses ISO-8601 duration format, for example PT1S.
retryScope string
INPUTNONEINPUTALLControls 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
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
username string
The database user
Outputs
rowCount integer
Total rows read
updatedCount integer
Rows inserted or updated
Metrics
query counter
queriesThe number of batch queries executed.
records counter
recordsThe number of records processed.
updated counter
recordsThe number of records updated.
