SQL Server

Configuration
Table Configuration
- Load Options
Binary types

This data destination connector sends data to a Microsoft SQL Server database.

Configuration

Create a new configuration of the SQL Server data destination connector.

The first step is to Set Up Database Credentials. You need to provide a host name, user name, password, database name, and SQL Server version; optionally, an instance name if needed for the connection.

We highly recommend that you create a dedicated credentials for the connector in your database. You can use the following SQL code to get started:

CREATE LOGIN writer_sample WITH PASSWORD = 'Writer_sample1';
CREATE DATABASE writer_sample;
USE writer_sample;
CREATE SCHEMA writer_sample_schema;
CREATE ROLE writer_sample_role;
CREATE USER writer_sample FROM LOGIN writer_sample WITH DEFAULT_SCHEMA=writer_sample_schema;
GRANT EXECUTE, CREATE TABLE TO writer_sample_role;
GRANT ALTER, SELECT, INSERT, UPDATE, DELETE ON SCHEMA :: writer_sample_schema TO writer_sample_role;
ALTER ROLE writer_sample_role ADD MEMBER writer_sample;

It is also possible to secure the connection using an SSH tunnel.

Table Configuration

The next step is to configure the tables to write. Click the Add new table button and select an existing table from Storage:

The next step is to specify table configuration. Use the preview icon to peek at the column contents.

For each column you can specify its

name in the destination database; you can also use the select box in the table header to bulk convert the case of all names.
data type (one of SQL Server data types); you can also use the select box in the table header to bulk set the type for all columns. Setting the data type to IGNORE means that column will not be present in the destination table.
nullable; when checked, the column will be marked as nullable and empty values ('') in that column will be converted to NULL. Use this for non-string columns with missing data.
default value; the provided value will be set as the default value of the column in the target table.

When done configuring the columns, don’t forget to save the settings.

Load Options

At the top of the page, you can specify the target table name and additional load options. There are two main options how the data destination connector can write data to tables — Full Load and Incremental Load.

In the Incremental Load mode, the data are bulk inserted into the destination table and the table structure must match (including the data types). That means the structure of the target table will not be modified. If the target table doesn’t exist, it will be created. If a primary key is defined on the table, the data is upserted. If no primary key is defined, the data is inserted.

In the Full Load mode, the table is completely overwritten including the table structure. The table is removed using the DROP command and recreated. The DROP command needs to acquire a table-level lock. This means that if the database is used by other applications which acquire table-level locks, the connector may freeze waiting for the locks to be released.

Additionally, you can specify a Primary key of the table, a simple column Data filter, and a filter for incremental processing.

Binary types

For binary types binary, varbinary, image, the following rules apply:

If value starts with 0x and length of the value is even number, then it is a HEX value according to the MsSQL specification (style 1).
- Example: "0xabcdef" is converted to binary value 0xabcdef.
Else, the value is a string. MsSQL adds separator \0 after each char code, when storing string as the binary type.
- Example: "0xabcde" is converted to binary value 0\0x\0a\0b\0c\0d\0e.
- Example: "dog" is converted to binary value d\0o\0g\0.