Microsoft SQL Server RDB Connector

Scans data from a Microsoft SQL Server instance.

Pre-Requisites

Microsoft SQL Server running

Configuration

Example credentials for this seed:

{
  "config": {
    "password": "Admin1234",
    "username": "sa"
  },
  "name": "MS SQL Server RDBMS credentials",
  "type": "rdbms-mssql-connector"
}

Example configuration in a seed:

{
  "type": "rdbms-mssql-connector",
  "name": "MS SQL Server RDBMS Seed",
  "config": {
    "seed": {
      "pull": {
        "objectSQL": "SELECT PersonID, LastName, FirstName, Address, City FROM Persons where PersonID = ?;",
        "id": "PersonID"
      },
      "scan": {
        "fields": {
          "id": "PersonID",
          "hash": [
            "PersonID"
          ]
        },
        "fullScanSQL": "SELECT PersonID, LastName, FirstName, Address, City FROM Persons;"
      },
      "url": "jdbc:sqlserver://localhost:1433;databaseName=test;"
    }
  },
  "pipelineId": "af856747-2343-46b1-a1d0-afd4d189d7c4",
  "credentialId": "004ec9f2-43f2-48b7-ae6f-8770bf6cc8e9",
  "scanAction": "scan",
  "processAction": "process",
  "batchSize": 100
}

Configuration parameters:

url

(Required, String) A JDBC connection url, containing the database name.

scan.fullScanSQL

(Required, String) SQL statement to be used for fetching record ids from the database.

scan.fields.id

(Required, String) Identifier column name on the database for each record.

scan.fields.hash

(Required, List<String>) List of fields used for hashing the data in order to provide incremental updates.

pull.objectSQL

(Optional, String) SQL statement to retrieve each record after scanning the data in the scan action.

pull.id

(Required, String) Identifier column name on the database for each record.

Scan action

Runs a SQL statement to extract records identifiers before processing each of them.

Example configuration in a processor:

{
  "config": {
    "seed": {
      "pull": {
        "objectSQL": "SELECT PersonID, LastName, FirstName, Address, City FROM Persons where PersonID = ?;",
        "id": "PersonID"
      },
      "scan": {
        "fields": {
          "id": "PersonID",
          "hash": [
            "PersonID"
          ]
        },
        "fullScanSQL": "SELECT PersonID, LastName, FirstName, Address, City FROM Persons;"
      },
      "url": "jdbc:sqlserver://localhost:1433;databaseName=test;"
    }
  },
  "scanAction": "scan"
}

Query action

Queries the database and process each record coming from the database as they are without need to scan identifiers first. Useful for running views and stored procedures.

Example configuration in a processor:

{
  "config": {
    "seed": {
      "scan": {
        "fields": {
          "id": "PersonID",
          "hash": [
            "PersonID"
          ]
        },
        "fullScanSQL": "exec dbo.uspGetRecords;"
      },
      "url": "jdbc:sqlserver://localhost:1433;databaseName=test;"
    }
  },
  "scanAction": "query"
}