...
| User Facing Name | Type | Description | Constraints |
|---|---|---|---|
| Label | String | Label for UI | |
| Host | String | MSSQL host (serverName) | Required (defaults to localhost on UI) |
| Port | Number | The port where SQL Server is listening. If the port number is specified in the connection string, no request to SQLbrowser is made. When the port and instanceName are both specified, the connection is made to the specified port. However, the instanceName is validated and an error is thrown if it does not match the port. Important: We recommend that the port number is always specified, as this is more secure than using SQLbrowser | Optional (default 1433) |
| Database | String | Database name to connect | Required |
| Authentication Type | Select | Indicates which SQL authentication method will be used for the connection. Use 'SQL Login' to connect to a SQL Server using username and password properties. 'Active Directory Password' can be used to connect to an Azure SQL Database/Data Warehouse using an Azure AD principal name and password | |
| Username | String | DB username | Required |
| Password | String | User password | Required |
| Connection Arguments | Keyvalue | A list of arbitrary string tag/value pairs as connection arguments, list of properties | |
| Database Command | String | Database command to run | Valid SQL query |
| Instance Name | String | The SQL Server instance name to connect to. When it is not specified, a connection is made to the default instance. For the case where both the instanceName and port are specified, see the notes for port. If you specify a Virtual Network Name in the Server connection property, you cannot use instanceName connection property | Optional |
| Query Timeout | Number | The number of seconds to wait before a timeout has occurred on a query. The default value is -1, which means infinite timeout. Setting this to 0 also implies to wait indefinitely. | Optional |
| Application Intent | Select | Declares the application workload type when connecting to a server. | Possible values: 'ReadWrite' and 'ReadOnly'. Default: 'ReadWrite'. |
| Connect Timeout | Number | Time in seconds to wait for a connection to the server before terminating the attempt and generating an error. | Optional |
| Column Encryption | Select | Default column encryption setting for all the commands on the connection. When enabled the JDBC driver will transparently encrypt and decrypt sensitive data stored in encrypted database columns in the SQL Server. | Possible values are: 'Enabled' and 'Disabled'. Default: 'Disabled'. |
| Encrypt | Select | When set to 'Yes', SQL Server uses SSL encryption for all data sent between the client and server if the server has a certificate installed. | Possible values are: 'Yes' and 'No'. Default: 'No'. |
| Trust Server Certificate | Select | When set to 'Yes' (and encryption enabled), SQL Server uses SSL encryption for all data sent between the client and server without validating the server certificate. | Possible values are: 'Yes' and 'No'. Default: 'No'. |
| Workstation ID | String | Used to identify the specific workstation in various SQL Server profiling and logging tools. | Optional |
| Failover Partner | String | The name or network address of the instance of SQL Server that acts as failover partner. | Optional |
| Packet Size | Number | The network packet size used to communicate with SQL Server, specified in bytes. It's not recommended to specify packet size property when the encryption is enabled. Otherwise, the driver might raise a connection error. | Optional |
| Current Language | String | Must correspond to the SQL Server language record name and specifies the language environment for the session. The session language determines the datetime formats and system messages. | Optional |
Data Types Mapping
| MS SQL Data Type | CDAP Schema Data Type | Support | Comment |
|---|---|---|---|
| BIGINT | Schema.Type.LONG | + | |
| BINARY | Schema.Type.BYTES | + | |
| BIT | Schema.Type.BOOLEAN | + | |
| CHAR | Schema.Type.STRING | + | |
| DATE | Schema.LogicalType.DATE | + | |
| DATETIME | Schema.LogicalType.TIMESTAMP_MICROS | + | |
| DATETIME2 | Schema.LogicalType.TIMESTAMP_MICROS | + | |
| DATETIMEOFFSET | Schema.Type.STRING | * | |
| DECIMAL | Schema.LogicalType.DECIMAL | + | |
| FLOAT | Schema.Type.FLOAT | + | |
| IMAGE | Schema.Type.BYTES | + | |
| INT | Schema.Type.INT | + | |
| MONEY | Schema.LogicalType.DECIMAL | + | |
| NCHAR | Schema.Type.STRING | + | |
| NTEXT | Schema.Type.STRING | + | |
| NUMERIC | Schema.LogicalType.DECIMAL | + | |
| NVARCHAR | Schema.Type.STRING | + | |
| NVARCHAR(MAX) | Schema.Type.STRING | + | |
| REAL | Schema.Type.FLOAT | + | |
| SMALLDATETIME | Schema.LogicalType.TIMESTAMP_MICROS | + | |
| SMALLINT | Schema.Type.INT | + | |
| SMALLMONEY | Schema.LogicalType.DECIMAL | + | |
| TEXT | Schema.Type.STRING | + | |
| TIME | Schema.LogicalType.TIME_MICROS | + | |
| TIMESTAMP | Schema.Type.BYTES | * | TIMESTAMP is the synonym for the ROWVERSION data type, values of which are automatically generated. Thus TIMESTAMP can not be supported by Sink plugin. |
| TINYINT | Schema.Type.INT | + | |
| UDT | Schema.Type.BYTES | + | |
| UNIQUEIDENTIFIER | Schema.Type.STRING | + | |
| VARBINARY | Schema.Type.BYTES | + | |
| VARBINARY(MAX) | Schema.Type.BYTES | + | |
| VARCHAR | Schema.Type.STRING | + | |
| VARCHAR(MAX) | Schema.Type.STRING | + | |
| XML | Schema.Type.STRING | + | |
| SQLVARIANT | Schema.Type.STRING | * | |
| GEOMETRY | Schema.Type.BYTES | + | |
| GEOGRAPHY | Schema.Type.BYTES | + |
Approach
Create a module mssql-plugin in database-plugins project, reuse existing database-plugins code if possible. Add MSSQL-specific properties to configuration, add support for MSSQL-specific datatypes. Update UI widgets JSON definitions.
...