> For the complete documentation index, see [llms.txt](https://docs.selfuel.digital/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.selfuel.digital/data-integration-with-nexus/nexus-elements/connectors/sink/sql-server.md).

# SQL Server

> JDBC SQL Server Sink Connector

### Support SQL Server Version[​](https://seatunnel.apache.org/docs/2.3.7/connector-v2/sink/SqlServer#support-sql-server-version) <a href="#support-sql-server-version" id="support-sql-server-version"></a>

* server:2008 (Or later version for information only)

### Description[​](https://seatunnel.apache.org/docs/2.3.7/connector-v2/sink/SqlServer#description) <a href="#description" id="description"></a>

Write data through jdbc. Support Batch mode and Streaming mode, support concurrent writing, support exactly-once semantics (using XA transaction guarantee).

### Key Features[​](https://seatunnel.apache.org/docs/2.3.7/connector-v2/sink/SqlServer#key-features) <a href="#key-features" id="key-features"></a>

* [x] &#x20;exactly-once
* [x] &#x20;cdc

> Use `Xa transactions` to ensure `exactly-once`. So only support `exactly-once` for the database which is support `Xa transactions`. You can set `is_exactly_once=true` to enable it.

### Supported DataSource Info[​](https://seatunnel.apache.org/docs/2.3.7/connector-v2/sink/SqlServer#supported-datasource-info) <a href="#supported-datasource-info" id="supported-datasource-info"></a>

<table><thead><tr><th>Datasource</th><th>Supported Versions</th><th>Driver</th><th>Url</th><th data-hidden>Maven</th></tr></thead><tbody><tr><td>SQL Server</td><td>support version >= 2008</td><td>com.microsoft.sqlserver.jdbc.SQLServerDriver</td><td>jdbc:sqlserver://localhost:1433</td><td><a href="https://mvnrepository.com/artifact/com.microsoft.sqlserver/mssql-jdbc">Download</a></td></tr></tbody></table>

### Data Type Mapping[​](https://seatunnel.apache.org/docs/2.3.7/connector-v2/sink/SqlServer#data-type-mapping) <a href="#data-type-mapping" id="data-type-mapping"></a>

| SQLserver Data Type                                             | Nexus Data Type                                                                                                                                              |
| --------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| BIT                                                             | BOOLEAN                                                                                                                                                      |
| <p>TINYINT<br>SMALLINT</p>                                      | SHORT                                                                                                                                                        |
| INTEGER                                                         | INT                                                                                                                                                          |
| BIGINT                                                          | LONG                                                                                                                                                         |
| <p>DECIMAL<br>NUMERIC<br>MONEY<br>SMALLMONEY</p>                | <p>DECIMAL((Get the designated column's specified column size)+1,<br>(Gets the designated column's number of digits to right of the<br>decimal point.)))</p> |
| REAL                                                            | FLOAT                                                                                                                                                        |
| FLOAT                                                           | DOUBLE                                                                                                                                                       |
| <p>CHAR<br>NCHAR<br>VARCHAR<br>NTEXT<br>NVARCHAR<br>TEXT</p>    | STRING                                                                                                                                                       |
| DATE                                                            | LOCAL\_DATE                                                                                                                                                  |
| TIME                                                            | LOCAL\_TIME                                                                                                                                                  |
| <p>DATETIME<br>DATETIME2<br>SMALLDATETIME<br>DATETIMEOFFSET</p> | LOCAL\_DATE\_TIME                                                                                                                                            |
| <p>TIMESTAMP<br>BINARY<br>VARBINARY<br>IMAGE<br>UNKNOWN</p>     | Not supported yet                                                                                                                                            |

### Sink Options[​](https://seatunnel.apache.org/docs/2.3.7/connector-v2/sink/SqlServer#sink-options) <a href="#sink-options" id="sink-options"></a>

| Name                                            | Type    | Required | Default | Description                                                                                                                                                                                                                                              |
| ----------------------------------------------- | ------- | -------- | ------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| url                                             | String  | Yes      | -       | The URL of the JDBC connection. Refer to a case: jdbc:sqlserver://localhost:1433;databaseName=mydatabase                                                                                                                                                 |
| driver                                          | String  | Yes      | -       | <p>The jdbc class name used to connect to the remote data source,<br>if you use sqlServer the value is <code>com.microsoft.sqlserver.jdbc.SQLServerDriver</code>.</p>                                                                                    |
| user                                            | String  | No       | -       | Connection instance user name                                                                                                                                                                                                                            |
| password                                        | String  | No       | -       | Connection instance password                                                                                                                                                                                                                             |
| query                                           | String  | No       | -       | Use this sql write upstream input datas to database. e.g `INSERT ...`,`query` have the higher priority                                                                                                                                                   |
| database                                        | String  | No       | -       | <p>Use this <code>database</code> and <code>table-name</code> auto-generate sql and receive upstream input datas write to database.<br>This option is mutually exclusive with <code>query</code> and has a higher priority.</p>                          |
| table                                           | String  | No       | -       | <p>Use database and this table-name auto-generate sql and receive upstream input datas write to database.<br>This option is mutually exclusive with <code>query</code> and has a higher priority.</p>                                                    |
| primary\_keys                                   | Array   | No       | -       | This option is used to support operations such as `insert`, `delete`, and `update` when automatically generate sql.                                                                                                                                      |
| support\_upsert\_by\_query\_primary\_key\_exist | Boolean | No       | false   | Choose to use INSERT sql, UPDATE sql to process update events(INSERT, UPDATE\_AFTER) based on query primary key exists. This configuration is only used when database unsupport upsert syntax. **Note**: that this method has low performance            |
| connection\_check\_timeout\_sec                 | Int     | No       | 30      | The time in seconds to wait for the database operation used to validate the connection to complete.                                                                                                                                                      |
| max\_retries                                    | Int     | No       | 0       | The number of retries to submit failed (executeBatch)                                                                                                                                                                                                    |
| batch\_size                                     | Int     | No       | 1000    | <p>For batch writing, when the number of buffered records reaches the number of <code>batch\_size</code> or the time reaches <code>checkpoint.interval</code><br>, the data will be flushed into the database</p>                                        |
| is\_exactly\_once                               | Boolean | No       | false   | <p>Whether to enable exactly-once semantics, which will use Xa transactions. If on, you need to<br>set <code>xa\_data\_source\_class\_name</code>.</p>                                                                                                   |
| generate\_sink\_sql                             | Boolean | No       | false   | Generate sql statements based on the database table you want to write to                                                                                                                                                                                 |
| xa\_data\_source\_class\_name                   | String  | No       | -       | <p>The xa data source class name of the database Driver, for example, SqlServer is <code>com.microsoft.sqlserver.jdbc.SQLServerXADataSource</code>, and<br>please refer to appendix for other data sources</p>                                           |
| max\_commit\_attempts                           | Int     | No       | 3       | The number of retries for transaction commit failures                                                                                                                                                                                                    |
| transaction\_timeout\_sec                       | Int     | No       | -1      | <p>The timeout after the transaction is opened, the default is -1 (never timeout). Note that setting the timeout may affect<br>exactly-once semantics</p>                                                                                                |
| auto\_commit                                    | Boolean | No       | true    | Automatic transaction commit is enabled by default                                                                                                                                                                                                       |
| common-options                                  |         | no       | -       | Sink plugin common parameters, please refer to [Sink Common Options](/data-integration-with-nexus/nexus-elements/connectors/sink/sink-common-options.md)[ Options](https://seatunnel.apache.org/docs/2.3.7/connector-v2/sink/common-options) for details |
| enable\_upsert                                  | Boolean | No       | true    | Enable upsert by primary\_keys exist, If the task has no key duplicate data, setting this parameter to `false` can speed up data import                                                                                                                  |

### tips[​](https://seatunnel.apache.org/docs/2.3.7/connector-v2/sink/SqlServer#tips) <a href="#tips" id="tips"></a>

> If partition\_column is not set, it will run in single concurrency, and if partition\_column is set, it will be executed in parallel according to the concurrency of tasks.

### Task Example[​](https://seatunnel.apache.org/docs/2.3.7/connector-v2/sink/SqlServer#task-example) <a href="#task-example" id="task-example"></a>

#### simple:[​](https://seatunnel.apache.org/docs/2.3.7/connector-v2/sink/SqlServer#simple) <a href="#simple" id="simple"></a>

> This is one that reads Sqlserver data and inserts it directly into another table

```
env {
  # You can set engine configuration here
  parallelism = 10
}

source {
  # This is a example source plugin **only for test and demonstrate the feature source plugin**
  Jdbc {
    driver = com.microsoft.sqlserver.jdbc.SQLServerDriver
    url = "jdbc:sqlserver://localhost:1433;databaseName=column_type_test"
    user = SA
    password = "Y.sa123456"
    query = "select * from column_type_test.dbo.full_types_jdbc"
    # Parallel sharding reads fields
    partition_column = "id"
    # Number of fragments
    partition_num = 10

  }
  # If you would like to get more information about how to configure Nexus and see full list of source plugins,
  # please go to source page
}

transform {

  # If you would like to get more information about how to configure Nexus and see full list of transform plugins,
  # please go to transform page
}

sink {
  Jdbc {
    driver = com.microsoft.sqlserver.jdbc.SQLServerDriver
    url = "jdbc:sqlserver://localhost:1433;databaseName=column_type_test"
    user = SA
    password = "Y.sa123456"
    query = "insert into full_types_jdbc_sink( id, val_char, val_varchar, val_text, val_nchar, val_nvarchar, val_ntext, val_decimal, val_numeric, val_float, val_real, val_smallmoney, val_money, val_bit, val_tinyint, val_smallint, val_int, val_bigint, val_date, val_time, val_datetime2, val_datetime, val_smalldatetime ) values( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )"

  }  # If you would like to get more information about how to configure Nexus and see full list of sink plugins,
  # please go to sink page
}
```

#### CDC(Change data capture) event[​](https://seatunnel.apache.org/docs/2.3.7/connector-v2/sink/SqlServer#cdcchange-data-capture-event) <a href="#cdcchange-data-capture-event" id="cdcchange-data-capture-event"></a>

> CDC change data is also supported by us In this case, you need config database, table and primary\_keys.

```
Jdbc {
  source_table_name = "customers"
  driver = com.microsoft.sqlserver.jdbc.SQLServerDriver
  url = "jdbc:sqlserver://localhost:1433;databaseName=column_type_test"
  user = SA
  password = "Y.sa123456"
  generate_sink_sql = true
  database = "column_type_test"
  table = "dbo.full_types_sink"
  batch_size = 100
  primary_keys = ["id"]
}
```

#### Exactly Once Sink[​](https://seatunnel.apache.org/docs/2.3.7/connector-v2/sink/SqlServer#exactly-once-sink) <a href="#exactly-once-sink" id="exactly-once-sink"></a>

> Transactional writes may be slower but more accurate to the data

```
  Jdbc {
    driver = com.microsoft.sqlserver.jdbc.SQLServerDriver
    url = "jdbc:sqlserver://localhost:1433;databaseName=column_type_test"
    user = SA
    password = "Y.sa123456"
    query = "insert into full_types_jdbc_sink( id, val_char, val_varchar, val_text, val_nchar, val_nvarchar, val_ntext, val_decimal, val_numeric, val_float, val_real, val_smallmoney, val_money, val_bit, val_tinyint, val_smallint, val_int, val_bigint, val_date, val_time, val_datetime2, val_datetime, val_smalldatetime ) values( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )"
    is_exactly_once = "true"

    xa_data_source_class_name = "com.microsoft.sqlserver.jdbc.SQLServerXADataSource"

  }  # If you would like to get more information about how to configure Nexus and see full list of sink plugins,
  # please go to sink page
```