> ## Documentation Index
> Fetch the complete documentation index at: https://docs.hotglue.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Microsoft SQL

# Connector Details

| Name           | Value                                                                                    |
| -------------- | ---------------------------------------------------------------------------------------- |
| Platform       | [Microsoft SQL](https://www.microsoft.com/en-us/sql-server)                              |
| Auth Type      | API Keys                                                                                 |
| Direction      | Bidirectional                                                                            |
| Tap Repo       | [https://github.com/hotgluexyz/tap-mssql](https://github.com/hotgluexyz/tap-mssql)       |
| Target Repo    | [https://github.com/hotgluexyz/target-mssql](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:

```json theme={null}
{
    "host": "your-server-hostname",
    "port": "1433",
    "database": "your_database",
    "user": "your_username",
    "password": "your_password"
}
```

## Example ETL Script

```python theme={null}
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:

```json theme={null}
{
    "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:

```json theme={null}
{
    "host": "your-server-hostname",
    "database": "your_database",
    "user": "your_username",
    "password": "your_password",
    "sqlalchemy_eng_params": {
        "fast_executemany": "True"
    }
}
```

## Connecting to Microsoft Fabric Warehouse

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

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](https://portal.azure.com) 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

<Warning>
  If you lose the client secret value, you will need to create a new secret.
</Warning>

### 4. Grant the app access to Fabric

* Open your workspace in [Microsoft Fabric](https://app.fabric.microsoft.com)
* 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

```json theme={null}
{
    "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:

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

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

### Option 2: Individual Connection Parameters

Specify each connection detail separately:

```json theme={null}
{
    "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`.

```json theme={null}
{
    "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

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

### With Per-Stream Control

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

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

```json theme={null}
{
    "streams": {
        "customers": {
            "truncate": true
        },
        "transactions": {
            "replication_method": "merge"
        }
    }
}
```