MS SQL DB Extractor

Supported MS SQL Versions

This component uses the Microsoft ODBC Driver for SQL Server, version 18.0.1.1-1, and supports the following versions:

• SQL Server 2012
• SQL Server 2014
• SQL Server 2016
• SQL Server 2017
• SQL Server 2019
• SQL Server 2022

Configuration

The config.json file contains the following properties within the parameters key:

• db
  • host – string
  • port (optional) – int (default: 1433)
  • database – string
  • user – string
  • #password – string
  • ssh (optional) – object: Settings for the SSH tunnel
    • enabled – bool
    • sshHost – string: IP address or hostname of the SSH server
    • sshPort (optional) – int: SSH server port (default: 22)
    • localPort (optional) – int: SSH tunnel local port in the Docker container (default: 33006)
    • user (optional) – string: SSH user (default same as db.user)
    • compression (optional) - bool: Enables SSH tunnel compression (default: false)
    • keys (optional) – SSH keys
      • public – string: Public SSH key
      • #private – string: Private SSH key
  • ssl (optional) – object
    • enabled (optional) – bool (default: false)
    • ca – string: Certificate file
    • verifyServerCert – bool
    • ignoreCertificateCn (optional) – bool (default: false)
• enabled (optional) – bool (default: true)
• name (optional) – string
• query (optional) – string (either query or table must be set)
• table (optional) – object (either query or table must be set)
  • schema – string
  • tableName – string
• columns (optional) – array of strings
• outputTable – string
• incremental (optional) – bool (default: false)
• incrementalFetchingColumn (optional) – string
• incrementalFetchingLimit (optional) – int
• primaryKey (optional) – array of strings
• retries (optional) – int: Number of PDO (fallback) retries if an error occurs (default: 5)
• maxTriesBcp (optional) – int: Number of BCP retries if an error occurs (default: 1)
• nolock (optional) – bool (default: false)
• disableBcp (optional) – bool: Do not use BCP for export (default: false)
• disableFallback (optional) – bool: Do not use PDO fallback for export (default: false)
• nolock (optional) – bool (default false)
• cdcMode (optional) – bool (default false)
• cdcModeFullLoadFallback (optional) – bool (default false)
• queryTimeout (optional) – int: Number of seconds before BCP and PDO exports time out (default: null)

Development

Clone this repository and initialize the workspace with the following commands:

git clone https://github.com/keboola/db-extractor-mssql
cd db-extractor-mssql
docker compose build
docker compose run --rm dev composer install --no-scripts

Run the test suite using this command:

docker compose run --rm dev composer tests

Setup Test Database

CREATE LOGIN tests_mssql_extractor WITH PASSWORD = '';
CREATE USER tests_mssql_extractor FOR LOGIN tests_mssql_extractor;
CREATE DATABASE tests_mssql_extractor;
USE tests_mssql_extractor;
CREATE SCHEMA tests;
CREATE TABLE tests.test (id text null, name text null);
INSERT INTO tests.test VALUES ('1', 'martin');
GRANT SELECT ON SCHEMA :: [tests] TO tests_mssql_extractor;