Skip to content

Latest commit

 

History

History
87 lines (76 loc) · 3.42 KB

README.md

File metadata and controls

87 lines (76 loc) · 3.42 KB
Raw

MS SQL DB Extractor

Supported MS SQL versions

The component is using Microsoft ODBC Driver for SQL Server in version 18.0.1.1-1 and supports following versions:

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

Configuration

The configuration config.json contains following properties in parameters key:

  • db
    • host – string
    • port (optional) – int (default 1433)
    • database – string
    • user – string
    • #password – string
    • ssh (optional) – object: Settings for SSH tunnel
      • enabled – bool
      • sshHost – string: IP address or hostname of SSH server
      • sshPort (optional) – int: SSH server port (default 22)
      • localPort (optional) – int: SSH tunnel local port in 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 (just one of query or table must be set)
  • table (optional) – object (just one of 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 occurred (default 5)
  • maxTriesBcp (optional) – int: Number of BCP retries if an error occurred (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 after which BCP as well as PDO exports will time out (default null)

Development

Clone this repository and init the workspace with following command:

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;