> For the complete documentation index, see [llms.txt](https://v2.dataos.info/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://v2.dataos.info/references/resources/nilus/batch/batch-sources/azure-synapse.md).

# Azure Synapse

Connector notes for Azure Synapse as a batch source: why there is no dedicated synapse:// scheme, connecting via the MS SQL Server path, and dedicated-pool caveats.

[Azure Synapse Analytics](https://learn.microsoft.com/azure/synapse-analytics/) is a SQL-based analytics service from Microsoft. Nilus supports Synapse reads through the **MS SQL Server batch source path** because Synapse dedicated SQL pools speak T-SQL over TDS. There is no separate `synapse://` source scheme; use `mssql://` or a DataOS depot configured for the SQL Server/MSSQL driver.

{% hint style="info" %}
Synapse **is** also supported as a Nilus **destination** for writing data into a Synapse pool.
{% endhint %}

## Connector status

| Item                 | Status                                                                       |
| -------------------- | ---------------------------------------------------------------------------- |
| Native source scheme | No separate `synapse://` source scheme.                                      |
| Recommended path     | Use the MS SQL Server batch source against a dedicated Synapse SQL pool.     |
| Pipeline type        | Batch pipeline using the `mssql://` source path or a SQL Server/MSSQL depot. |
| Destination support  | Synapse is supported as a Nilus destination.                                 |

## Supported source path

Use the [MS SQL Server (Batch)](https://v2.dataos.info/references/resources/nilus/batch/batch-sources/ms-sql-server.md) source against a Synapse dedicated SQL pool. Synapse's dedicated pool speaks T-SQL over TDS, so the `mssql://` scheme can connect and run `SELECT` queries against it. Caveats apply:

* **Dedicated SQL pool only**: serverless SQL pool support is not validated through this path. Behavior on serverless varies by query and may hit timeouts unrelated to Nilus.
* **Identifier and type quirks**: Synapse's T-SQL dialect is close to but not identical to SQL Server. Hand-test types and `incremental_key` semantics on a representative table before committing a production schedule.
* **Connection string**: use the Synapse-provided SQL endpoint (`<workspace>.sql.azuresynapse.net`) and a SQL or Azure AD identity with `SELECT` on the schemas you target.
* **Depot configuration**: connect via a DataOS depot configured for the SQL Server/MSSQL driver and pointed at the Synapse endpoint, or via direct `mssql://` URI with credentials projected through `spec.use.projection`.

This is the supported source path for compatible dedicated SQL pool workloads.

## Requirements

* Use a Synapse dedicated SQL pool endpoint; serverless SQL pool behavior is not validated through this path.
* Grant the projected SQL or Azure AD identity `SELECT` on every schema/table referenced by `source_table`.
* Use the Synapse SQL endpoint (`<workspace>.sql.azuresynapse.net`) with the SQL Server driver settings required by your environment.
* Test source SQL, data types, and `incremental_key` behavior on a representative table before scheduling production runs.

## Connection and driver options

Synapse uses the same ODBC contract as MS SQL Server (Batch). Depot-backed pipelines inherit these as URI query parameters when Nilus builds the `mssql://` address from the depot.

| Option                              | Required       | Default (depot path)            | Description                                                                                                                                |
| ----------------------------------- | -------------- | ------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------ |
| `host`                              | Yes            | depot `host`                    | Synapse SQL endpoint, typically `<workspace>.sql.azuresynapse.net`.                                                                        |
| `port`                              | No             | depot `port` (usually `1433`)   | TDS port.                                                                                                                                  |
| `database`                          | Yes            | depot `database`                | Dedicated SQL pool / database name.                                                                                                        |
| `username` / `password`             | Yes (SQL auth) | depot secret                    | SQL login. Azure AD token auth uses the MS SQL Server URI shape instead.                                                                   |
| `driver`                            | No             | `ODBC Driver 18 for SQL Server` | Must match a driver installed in the Nilus image.                                                                                          |
| `Encrypt`                           | No             | driver default                  | `yes` to require TLS.                                                                                                                      |
| `TrustServerCertificate`            | No             | `yes` on the depot path         | `yes` skips certificate validation. Prefer a trusted CA in production.                                                                     |
| `ignore_no_transaction_on_rollback` | No             | `false`                         | Set `true` when Synapse or SQL Server raises a no-transaction-on-rollback error during extract. See Source-specific environment variables. |

## Sample Nilus configs

### Batch — depot source (recommended)

Point `source.address` at a DataOS SQL Server/MSSQL depot whose host is the Synapse SQL endpoint. Depot credentials are inferred; you still project connector tuning env vars when you need them.

```yaml
name: synapse-orders-batch
version: v1alpha
type: nilus
spec:
  type: batch
  compute: universe-compute
  use:
    projection:
      projections:
        envVars:
          - key: MSSQL_IGNORE_NO_TRANSACTION_ON_ROLLBACK
            template: "true"
  source:
    address: dataos://synapse_depot?purpose=ro
    options:
      source_table: dbo.orders
      incremental_key: modified_at
      primary_key: order_id
  sink:
    address: dataos://analyticslakehouse?purpose=rw
    options:
      dest_table: synapse.orders
      incremental_strategy: merge
```

If you own the depot, you can set the same flag on the depot resource (`params.ignore_no_transaction_on_rollback: "true"`) instead of projecting the env var. When that param is absent, Nilus reads `MSSQL_IGNORE_NO_TRANSACTION_ON_ROLLBACK` (default `false`).

### Batch — direct `mssql://` URI

```yaml
name: synapse-orders-batch
version: v1alpha
type: nilus
spec:
  type: batch
  compute: universe-compute
  use:
    projection:
      secrets:
        - id: engineering:synapse-secret
          contextAlias: synsecret
      projections:
        envVars:
          - key: SYN_USER
            template: "{{ secrets['synsecret'].user | base64_decode }}"
          - key: SYN_PASSWORD
            template: "{{ secrets['synsecret'].password | base64_decode }}"
  source:
    address: mssql://{SYN_USER}:{SYN_PASSWORD}@myworkspace.sql.azuresynapse.net/analyticsdb?driver=ODBC+Driver+18+for+SQL+Server&Encrypt=yes&TrustServerCertificate=no&ignore_no_transaction_on_rollback=true
    options:
      source_table: dbo.orders
      incremental_key: modified_at
  sink:
    address: dataos://analyticslakehouse?purpose=rw
    options:
      dest_table: synapse.orders
      incremental_strategy: merge
```

On a **direct URI**, the rollback flag must be a query parameter (`ignore_no_transaction_on_rollback=true`). The `MSSQL_IGNORE_NO_TRANSACTION_ON_ROLLBACK` env var is applied when Nilus builds the URI from a **depot**.

The URI is the standard `mssql://` shape; Nilus does not validate that the host is actually Synapse vs SQL Server. If you hit a feature mismatch (e.g. a stored procedure or system view that exists in SQL Server but not in Synapse), the failure surfaces as a T-SQL syntax / object-not-found error rather than a connector error.

## Behavior and capabilities

* **Pipeline type**: Synapse reads run as a `batch` pipeline through the MS SQL Server source path.
* **Object selection**: set `source.options.source_table` to a table or view that the Synapse endpoint can serve through TDS.
* **Fetch semantics**: Nilus behaves like a SQL Server batch reader; Synapse-specific SQL differences are surfaced as source query errors.
* **Operational fit**: use this for dedicated SQL pool workloads that behave like ordinary T-SQL table reads.
* **Unsupported behavior**: Nilus does not provide a `synapse://` source address or validate serverless SQL pool behavior.
* **Transactions** — dedicated SQL pools do not always expose SQL Server transaction semantics. That can surface as an ODBC rollback error. Set `ignore_no_transaction_on_rollback` (depot param, URI query, or `MSSQL_IGNORE_NO_TRANSACTION_ON_ROLLBACK`) when you hit it.
* **Lakehouse decimals** — table reads preserve source precision and scale. `query:` extracts store decimals as Iceberg `decimal(38,9)`. Do not mix these extract styles on the same `dest_table`. See [Custom Query (SQL Sources)](https://v2.dataos.info/references/resources/nilus/batch/custom-sources/custom-query-sql-sources.md) and [Schema evolution](https://v2.dataos.info/references/resources/nilus/concepts/schema-evolution.md).

## Troubleshooting

| Symptom                                                                                    | Likely cause                                                                                                                                      | Resolution                                                                                                                                                                                                                                                                                                                            |
| ------------------------------------------------------------------------------------------ | ------------------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Unsupported source scheme for `synapse://`                                                 | Synapse does not have a separate Nilus source scheme.                                                                                             | Use the `mssql://` source path or a SQL Server/MSSQL depot against a dedicated SQL pool.                                                                                                                                                                                                                                              |
| T-SQL object or syntax errors                                                              | The query path uses SQL Server behavior that differs in Synapse.                                                                                  | Test the query directly against the Synapse endpoint and adjust the table/view or SQL shape.                                                                                                                                                                                                                                          |
| Authentication or encryption failure                                                       | Driver, identity, or TLS settings do not match the Synapse endpoint requirements.                                                                 | Verify the SQL endpoint, projected credentials, and `Encrypt` / certificate settings.                                                                                                                                                                                                                                                 |
| Extract fails with a no-transaction / rollback error                                       | Synapse (or some SQL Server setups) does not start a transaction the driver expected.                                                             | For a depot source, project `MSSQL_IGNORE_NO_TRANSACTION_ON_ROLLBACK=true`. For a direct URI, add `ignore_no_transaction_on_rollback=true` to the query string.                                                                                                                                                                       |
| `ValueError: Cannot serialize value, scale of value does not match type decimal(38, 9): 4` | The Lakehouse table was created by a `query:` extract (`decimal(38,9)`), then a table read tried to write source-scale decimals — or the reverse. | Keep one extract style per table. Point `dest_table` at a new table, or drop and reload. See [Schema evolution](https://v2.dataos.info/references/resources/nilus/concepts/schema-evolution.md) and [Custom Query (SQL Sources)](https://v2.dataos.info/references/resources/nilus/batch/custom-sources/custom-query-sql-sources.md). |

## Roadmap

A dedicated `synapse://` source scheme may be added in a future release; today Synapse source reads go through the MS SQL Server source path. If you have a Synapse-as-source use case the `mssql://` path can't cover, open a request with your Nilus contact so it can be sequenced.

## Related docs

* [Batch data movement](https://v2.dataos.info/references/resources/nilus/batch.md)
* [Batch pipeline config](https://v2.dataos.info/references/resources/nilus/batch/pipeline-config.md)
* [Batch sample configs](https://v2.dataos.info/references/resources/nilus/batch/sample-configs.md)
* [MS SQL Server](https://v2.dataos.info/references/resources/nilus/batch/batch-sources/ms-sql-server.md)
* [Secrets and projections](https://v2.dataos.info/references/resources/nilus/concepts/secrets-and-projections.md)


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation by asking a question.

Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters:

```
GET https://v2.dataos.info/references/resources/nilus/batch/batch-sources/azure-synapse.md?ask=<question>&goal=<user_goal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `automate deployments from our CI pipeline` lets GitBook tailor the answer to that use case.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.
