ePrivacy and GPDR Cookie Consent by Cookie Consent Skip to main content

Loader MsSQL

MsSQL loader exports data to Microsoft SQL server database.

Prerequisite

When connecting to an Azure database and using an SSH tunnel, the username must be in the format <username>@<databasename> instead of just <username> otherwise, it will throw errors.

Data In/ Data Out

Data In Upload all files in /data/in/files
Data Out N/A

 

Learn more: about the folder structure here.

Parameters

SQL1.png

Host (required) Name of server that hosts the database.
Port (required) The port number you want to access. 
Database (required) Name of the database.

Username (required)

 Account name.
Password (required) Account password.

 

SQL2.png

Enabled (optional) IfChecked youif wantSSH a certain tablerequired to beconnect pulled from theto database. 
Private (optional) Private key. 
Public (optional) Public key.
SSH Host (optional) Name of SSH Host.host.
SSH User (optional) Username of SSH Host. 

 

SQL3.png

Input Table (required)

 The name of the input table you want to load to the database. 
DB Table Name (required) The name of the table that will be created in the database. 
Export (optional) True if you want this table to be loader or not. By default, it is true.
Incremental (optional)

Signifies if you want to load the data by overwriting whatever is in database or incrementally.

 
Column (required)

DefinesCan be defined for table either when using incremental load, or not. 

 

Primary key constraint can be defined on multiple columns or one, but cannot contain NULL values. If incremental load is enabled, then only rows with new or updated records are inserted.

 

If the incremental load is disabled but primary keys are defined: full table is loaded (and will replace whatever is in destination database), but primary key column. Can be used with incremental load.

Incremental load with primary key - just rows with unique primary keycolumns will be loaded.

Primary keys witnout incremental - all rows loaded, but columns setshow as primary keykeys also in loaderdestination willtable bestructure.

also PKs in destination.

 


SQL4.png

Column Name (required) Refers to the column in the input csvCSV file.
DB Column Name (required) Refers to how this column will be named in the destination database.

Data Type (required)

 The data type used in the column.

Size (optional)

 The maximum number of digits used by the data type of the column or parameter.

Nullable (optional)

If youchecked, wantthen the data with empty strings are loaded as NULL values in destination database (in this case is_nullable = YES).

 

If unchecked and data contains empty strings, then loader returns an error message. For example: “Cannot insert the value inNULL thisinto column toXY, becolumn nullable.does not allow nulls. INSERT fails.”

 

Warning: All columns listed as primary keys should have unchecked Nullable (is_nullable = NO), otherwise loader throws an error “Cannot define PRIMARY KEY constraint on nullable column”.

 
Default Value (optional)

The default value that is inserted for empty values in the column. For example,  inserts NULLs in destination columns database for empty strings in csv file. - inserts - for empty strings.

 

 

Back to top