Start typing to search.

Connector Reference

MS SQL Server

View Markdown

Configure the mssql connector for SQL Server or Azure SQL, query and write tables, and run read-only SQL on an ApplicationIntent ReadOnly pool.

MS SQL Server

The mssql connector queries and writes Microsoft SQL Server (including Azure SQL) tables over its own connection pool, with a separate read-only pool for dashboard queries.

When to use it

Use mssql as a relational source or destination on SQL Server, and as a mapping database or cursor store for sync tasks. For PostgreSQL or MySQL, use the PostgreSQL or MySQL connectors.

Configuration

Key Type Required Default Description
server string Yes — Server hostname or IP
database string Yes — Database name
user string Yes — Login username
password string Yes — Login password (stored as a secret)
port number No 1433 Server port
encrypt boolean No true Encrypt connection (required for Azure SQL)
trust_server_certificate boolean No false Trust self-signed certificates
pool_size number No 4 Number of parallel connections
readonly_pool_size number No 2 Connections in the read-only pool (ApplicationIntent ReadOnly)

Methods

Method Arguments Returns Behavior
find table (required), filter, select, limit, offset, order_by array Streams rows with backpressure: the driver request pauses when the internal queue reaches 1000 rows and resumes as downstream consumes
find_one table (required), filter, select object Returns the first matching row
insert_one table (required), data (required) object Inserts a single record
upsert table (required), data (required), conflict_columns (required), update_columns object Inserts or updates using a MERGE statement keyed on conflict_columns
delete table (required), filter object Deletes rows matching the filter
execute statements (required) object Executes a raw SQL statement
select sql (required), params array Runs a read-only SELECT on the read-only pool (SELECT or WITH only; ApplicationIntent ReadOnly)

filter uses the shared JSON filter syntax with gt, gte, lt, lte, ne, in, nin, like, ilike, and exists, plus and/or nesting. ilike compiles to LOWER(col) LIKE LOWER(pattern); the regex operator is not supported on SQL Server.

Example

name: export_crm_accounts
connectors:
  source:
    type: mssql
steps:
  - name: read_accounts
    type: call_method
    config: |
      {
        "connector": "source",
        "method": "find",
        "args": { "table": "accounts", "filter": { "is_active": true } }
      }

The matching connectors.yaml entry for CLI runs:

source:
  type: mssql
  config:
    server: sql.example.internal
    database: crm
    user: envoy_reader
    password: ${MSSQL_PASSWORD}

Behavior notes

  • select rejects anything that is not a SELECT or WITH ... SELECT statement and runs on connections opened with ApplicationIntent ReadOnly, so dashboard queries can be routed to a readable secondary.
  • The connector implements the mapping-DB interface (a resource_mapping table created on first use) and the cursor store, so it can serve as lookup_connector or cursor_store for sync tasks. See Idempotent sync and the mapping DB.
  • Connection pooling is managed by the connector itself (pool_size parallel tedious connections); size it to the task's step concurrency.