Microsoft SQL Server | Polytomic Docs

Supports:

✅ Models
✅ Model sync destination
✅ Bulk sync source
✅ Bulk sync destination

Connection

Configuration

Name	Type	Description	Required
`change_detection`	`boolean`	Use change data capture for bulk syncs	`false`
`database`	`string`	Database	`true`
`hostname`	`string`	Server	`true`
`password`	`string`	Password	`true`
`port`	`integer`	Port	`true`
`ssh`	`boolean`	Connect over SSH tunnel	`false`
`ssh_host`	`string`	SSH host	`false`
`ssh_port`	`integer`	SSH port	`false`
`ssh_private_key`	`string`	Private key	`false`
`ssh_user`	`string`	SSH user	`false`
`ssl`	`boolean`	Use SSL	`false`
`username`	`string`	Username	`true`

Example

1 {
2   "name": "Microsoft SQL Server connection",
3   "type": "mssql",
4   "configuration": {
5     "change_detection": false,
6     "database": "sampledb",
7     "hostname": "example.database.windows.net",
8     "password": "secret",
9     "port": 1433,
10     "ssh": false,
11     "ssh_host": "bastion.example.com",
12     "ssh_port": 22,
13     "ssh_private_key": "",
14     "ssh_user": "",
15     "ssl": false,
16     "username": "user"
17   }
18 }

Model Sync

Source

Configuration

Name	Type	Description	Required
`query`	`string`		`false`
`table`	`string`	Table	`false`
`view`	`string`	View	`false`

Example

1 {
2   ...
3   "configuration": {
4     "query": "SELECT * from users",
5     "table": "users",
6     "view": "active_users"
7   }
8 }

Target

Microsoft SQL Server connections may be used as the destination in a model sync.

All targets

Configuration

Name	Type	Description	Required
`created_column`	`string`	’Created at’ timestamp column	`false`
`preserve_table_on_resync`	`boolean`	Preserve destination table when resyncing	`false`
`updated_column`	`string`	’Updated at’ timestamp column	`false`
`write_null_values`	`boolean`	Copy null values When enabled updates will set fields to NULL when the source value is null	`false`
`write_record_timestamps`	`boolean`	Write row timestamp metadata	`false`

Example

1 {
2   ...
3   "target": {
4       "configuration": {
5         "created_column": "",
6         "preserve_table_on_resync": false,
7         "updated_column": "",
8         "write_null_values": false,
9         "write_record_timestamps": false
10     }
11   }
12 }

Bulk Sync

Destination

Configuration

Name	Type	Description	Required
`advanced`	`object`		`false`
`mirror_schemas`	`boolean`	Mirror schemas	`false`
`schema`	`string`	Output schema	`false`

Example

1 {
2   ...
3   "destination_configuration": {
4     "advanced": {
5         "empty_strings_null": false,
6         "hard_deletes": false,
7         "initial_execution": "",
8         "table_prefix": "",
9         "truncate_existing": false
10     },
11     "mirror_schemas": false,
12     "schema": "schema"
13   }
14 }

Type handling

Destination types

POLYTOMIC TYPE	MICROSOFT SQL SERVER TYPE
`array<>`	`NVARCHAR(MAX)`
`bigint`	`BIGINT`
`boolean`	`BIT`
`date`	`DATE`
`datetime`	`DATETIMEOFFSET`
`decimal(precision, scale)`	`DECIMAL(precision,scale)`
`double`	`FLOAT`
`int`	`INT`
`json`	`NVARCHAR(MAX)`
`jsonarray`	`NVARCHAR(MAX)`
`number`	`NUMERIC(38,10)`
`object{}`	`NVARCHAR(MAX)`
`single`	`REAL`
`smallint`	`SMALLINT`
`string`	`NVARCHAR(4000)`
`time`	`TIME`

Source types

MICROSOFT SQL SERVER TYPE	POLYTOMIC TYPE
`BIGINT`	`bigint`
`CHAR`	`string`
`DATE`	`date`
`DATETIME`	`datetime`
`DATETIME2`	`datetime`
`DATETIMEOFFSET`	`datetime`
`DECIMAL`	`number`
`DECIMAL(precision, scale)`	`decimal(precision, scale)`
`FLOAT`	`double`
`INT`	`int`
`MONEY`	`decimal(precision, scale)`
`NCHAR`	`string`
`NTEXT`	`string`
`NUMBER(precision, scale)`	`decimal(precision, scale)`
`NUMERIC`	`number`
`NUMERIC(precision, scale)`	`decimal(precision, scale)`
`NVARCHAR`	`string`
`REAL`	`single`
`SMALLDATETIME`	`datetime`
`SMALLINT`	`smallint`
`SMALLMONEY`	`decimal(precision, scale)`
`TEXT`	`string`
`TIME`	`time`
`TINYINT`	`smallint`
`UNIQUEIDENTIFIER`	`string`
`VARCHAR`	`string`
`XML`	`string`