Connector Details

Name	Value
Platform	Microsoft SQL
Auth Type	API Keys
Direction	Bidirectional
Tap Repo	https://github.com/hotgluexyz/tap-mssql
Target Repo	https://github.com/hotgluexyz/target-mssql
Tap Metrics	Usage:
Target Metrics	Usage:

Tap MSSQL

Config

tap-mssql requires the following connection parameters to be specified in the config:

{
    "host": "your-server-hostname",
    "port": "1433",
    "database": "your_database",
    "user": "your_username",
    "password": "your_password"
}

Example ETL Script

import gluestick as gs
import os

# Define standard Hotglue directories
ROOT_DIR = os.environ.get("ROOT_DIR", ".")
INPUT_DIR = f"{ROOT_DIR}/sync-output"
OUTPUT_DIR = f"{ROOT_DIR}/etl-output"

# Read sync output
input = gs.Reader()

# Get tenant id
tenant_id = os.environ.get('USER_ID', os.environ.get('TENANT', 'default'))

# Iterate through the different streams in the sync output
for key in eval(str(input)):
    input_df = input.get(key)

    """
    Here we get the metadata for extracting the key properties, such as primary and replication keys.
    The database export targets will utilize these primary keys when upserting data.
    If you wish to hardcode your choice of primary keys, you can do so here.
    """
    metadata = input.get_metadata(key)
    if metadata.get("key_properties"):
        # Use the key_properties (e.g. primary and replication keys) from the catalog
        key_properties = eval(metadata["key_properties"])
    else:
        key_properties = []

    # Include tenant_id as a field if desired
    input_df["tenant"] = tenant_id

    # Write this stream to the OUTPUT directory with the specified key_properties
    gs.to_singer(input_df, key, OUTPUT_DIR, keys=key_properties)

Optional config flags

Property	Description	Default
`dialect`	The SQLAlchemy dialect	`mssql`
`driver_type`	The Python driver used to connect to SQL Server (`pymssql` or `pyodbc`)	`pymssql`
`sqlalchemy_eng_params`	SQLAlchemy engine parameters (e.g., `fast_executemany`, `future`)	`None`
`sqlalchemy_url_query`	SQLAlchemy URL query options passed through to ODBC	`None`
`batch_config`	Optional batch message configuration	`None`
`start_date`	The earliest record date to sync	`None`
`hd_jsonschema_types`	Enable higher-defined JSON Schema types to assist targets	`false`
`stream_maps`	Config object for stream maps capability	`None`
`stream_map_config`	User-defined config values for map expressions	`None`
`flattening_enabled`	Enable schema flattening and expand nested properties	`None`
`flattening_max_depth`	The max depth to flatten schemas	`None`
`lookback_window_days`	Number of past days to include when pulling data	`None`

The `sqlalchemy_url_query` Option

When using pyodbc as the driver_type, you can pass ODBC-specific options. This is useful when connecting via Azure Active Directory or when certificate trust settings are required:

{
    "host": "your-server-hostname",
    "database": "your_database",
    "user": "your_username",
    "password": "your_password",
    "driver_type": "pyodbc",
    "sqlalchemy_url_query": {
        "driver": "ODBC Driver 18 for SQL Server",
        "TrustServerCertificate": "yes",
        "Authentication": "ActiveDirectoryPassword"
    }
}

The `sqlalchemy_eng_params` Option

Enable fast_executemany to significantly reduce round trips to the server and improve bulk insert performance:

{
    "host": "your-server-hostname",
    "database": "your_database",
    "user": "your_username",
    "password": "your_password",
    "sqlalchemy_eng_params": {
        "fast_executemany": "True"
    }
}

Connecting to Microsoft Fabric Warehouse

Use an Entra service principal to connect to Microsoft Fabric Warehouse — no username/password required.

Follow the steps below to set up service principal authentication for Microsoft Fabric.

1. Register an app in Entra ID

Navigate to the Azure Portal and go to Azure Active Directory → App registrations → New registration
Give the app a name (e.g., tap-mssql-fabric) and select Single tenant
Click Register

2. Get your Client ID

On the app’s Overview page, copy the Application (client) ID
This value will be used as user in the tap config

3. Create a client secret

Go to Manage → Certificates & secrets → New client secret
Add a description and choose an expiry period, then click Add
Copy the secret Value immediately — it is only shown once and will be used as password in the tap config

If you lose the client secret value, you will need to create a new secret.

4. Grant the app access to Fabric

Open your workspace in Microsoft Fabric
Add the app as a member with at minimum a Viewer role
Confirm the app has access to both the workspace and the specific database in your config

5. Get the warehouse host

In Microsoft Fabric, open your workspace and select the Warehouse
Go to Settings (gear icon) → SQL Endpoint
Copy the SQL connection string — it follows this format: <warehouse-id>.datawarehouse.fabric.microsoft.com

Config for Fabric with Entra service principal

{
    "dialect": "mssql",
    "driver_type": "pyodbc",
    "host": "your-warehouse.datawarehouse.fabric.microsoft.com",
    "database": "your_warehouse",
    "user": "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx",
    "password": "your-client-secret-value",
    "sqlalchemy_url_query": {
        "driver": "ODBC Driver 18 for SQL Server",
        "Authentication": "ActiveDirectoryServicePrincipal"
    }
}

Target MSSQL

target-mssql is a Singer Target that loads data from your integrations into a Microsoft SQL Server database.

Config

You can configure the target using one of two approaches:

Option 1: Connection String

Use a single connection string that includes all database details:

{
    "sqlalchemy_url": "mssql+pyodbc://username:password@host:port/database?driver=ODBC+Driver+17+for+SQL+Server&Encrypt=yes&TrustServerCertificate=yes"
}

If your password contains special characters, URL-encode them (e.g., @ becomes %40).

Option 2: Individual Connection Parameters

Specify each connection detail separately:

{
    "user": "your_username",
    "password": "your_password",
    "host": "your-server-hostname",
    "port": "1433",
    "database": "your_database_name"
}

Parameter	Description	Example
`user`	Database username	`"sa"` or `"admin_user"`
`password`	Database password	`"P@55w0rd"`
`host`	Server hostname or IP address	`"localhost"` or `"sql.company.com"`
`port`	SQL Server port (default: 1433)	`"1433"`
`database`	Target database name	`"my_warehouse"`

Additional Settings

Setting	Description	Default
`default_target_schema`	Schema where tables will be created	`"dbo"`
`truncate`	Drop and recreate all tables before loading (global)	`false`
`input_path`	Directory containing per-stream configuration file	Not set

Per-Stream Configuration

For advanced control over specific tables, create a target-tables-config.json in the directory specified by input_path.

{
    "streams": {
        "users": {
            "truncate": true,
            "replication_method": "truncate"
        },
        "orders": {
            "truncate": false,
            "replication_method": "merge"
        }
    }
}

Options per stream:

Option	Description
`truncate`	Drop and recreate this specific table before loading
`replication_method`	`"truncate"` replaces the entire table; `"merge"` upserts data using the primary key

Configuration Examples

Basic Setup

{
    "user": "sa",
    "password": "P@55w0rd",
    "host": "localhost",
    "port": "1433",
    "database": "mydatabase",
    "default_target_schema": "dbo"
}

With Per-Stream Control

{
    "user": "sa",
    "password": "P@55w0rd",
    "host": "localhost",
    "port": "1433",
    "database": "mydatabase",
    "input_path": "./etl-output"
}

Then create target-tables-config.json in ./etl-output:

{
    "streams": {
        "customers": {
            "truncate": true
        },
        "transactions": {
            "replication_method": "merge"
        }
    }
}

Getting Started

Key Concepts

Managed authentication

Transformation

Custom Connectors

Unified Schema

Environment Settings

CLI

Connectors

Microsoft SQL

Connector Details

Tap MSSQL

Config

Example ETL Script

Optional config flags

The `sqlalchemy_url_query` Option

The `sqlalchemy_eng_params` Option

Connecting to Microsoft Fabric Warehouse

1. Register an app in Entra ID

2. Get your Client ID

3. Create a client secret

4. Grant the app access to Fabric

5. Get the warehouse host

Config for Fabric with Entra service principal

Target MSSQL

Config

Option 1: Connection String

Option 2: Individual Connection Parameters

Additional Settings

Per-Stream Configuration

Configuration Examples

Basic Setup

With Per-Stream Control

​Connector Details

​Tap MSSQL

​Config

​Example ETL Script

​Optional config flags

​The sqlalchemy_url_query Option

​The sqlalchemy_eng_params Option

​Connecting to Microsoft Fabric Warehouse

​1. Register an app in Entra ID

​2. Get your Client ID

​3. Create a client secret

​4. Grant the app access to Fabric

​5. Get the warehouse host

​Config for Fabric with Entra service principal

​Target MSSQL

​Config

​Option 1: Connection String

​Option 2: Individual Connection Parameters

​Additional Settings

​Per-Stream Configuration

​Configuration Examples

​Basic Setup

​With Per-Stream Control

Connector Details

Tap MSSQL

Config

Example ETL Script

Optional config flags

The `sqlalchemy_url_query` Option

The `sqlalchemy_eng_params` Option

Connecting to Microsoft Fabric Warehouse

1. Register an app in Entra ID

2. Get your Client ID

3. Create a client secret

4. Grant the app access to Fabric

5. Get the warehouse host

Config for Fabric with Entra service principal

Target MSSQL

Config

Option 1: Connection String

Option 2: Individual Connection Parameters

Additional Settings

Per-Stream Configuration

Configuration Examples

Basic Setup

With Per-Stream Control