Documentation by ClonePartner

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.