# Create a Sync from MSSQL to Batch Profile Events

### Before you start

To create an MSSQL → Batch Profile Events sync, you'll need:

* Access to the **Batch dashboard**
* An **MSSQL-compatible database** (Azure SQL Database, Azure SQL Managed Instance, Microsoft Fabric Warehouse, or SQL Server 2012+) containing one row per event
* Credentials to connect: either a **username/password** or a **Microsoft Entra ID service principal** (Client ID + Client Secret)
* A table (or view) that follows the **Cloud Sync events input format** (see below)

***

### 1) Prepare your MSSQL table or view

Cloud Sync expects your MSSQL source (table or view) to include:

1. A **profile identifier** (to know which profile the event belongs to)
2. An **event name** (the type of event being recorded)
3. A **cursor field** (to know which rows are new since the last run)
4. Any number of **event attribute columns** (sent to Batch as event properties)

***

#### 1.1 One row per event

Your source must contain **one row per event**.\
Unlike profile attribute syncs, multiple rows can share the same `custom_id` — each row creates a separate event on the corresponding profile.

***

#### 1.2 Required columns

Your table (or view) must include the following columns:

| Column            | Required | Description                                            |
| ----------------- | :------: | ------------------------------------------------------ |
| `custom_id`       |     ✅    | The identifier of the profile the event belongs to     |
| `event_name`      |     ✅    | The name of the event (e.g. `add_to_cart`, `purchase`) |
| `last_updated_at` |     ✅    | Cursor used for incremental sync                       |

{% hint style="warning" %}
`last_updated_at` must be set **at the time the event row is inserted** and must not change afterwards. It is used only to determine which rows to fetch on the next sync run, not as the event timestamp.
{% endhint %}

***

#### 1.3 Optional columns

| Column       | Description                                                                                                                         |
| ------------ | ----------------------------------------------------------------------------------------------------------------------------------- |
| `event_time` | The timestamp of the event, in **RFC 3339 UTC** format (e.g. `2026-04-17T04:25:00Z`). If omitted, Batch uses the time of reception. |

***

#### 1.4 Event attribute naming rules

SQL column names cannot contain characters like `$`, `(`, or `)`. Cloud Sync relies on **prefixes** in column names to represent typed event attributes.

**Supported prefixes**

| Prefix   | Meaning        | Example              |
| -------- | -------------- | -------------------- |
| `date__` | Date attribute | `date__purchased_at` |
| `url__`  | URL attribute  | `url__item_url`      |

Any column without a prefix is sent to Batch as a standard string, number, or boolean attribute.

***

#### 1.5 Handling objects

Object event attributes must be **stored as a JSON string** in your MSSQL column. The connector parses the string and sends it in the correct format to the Profile API.

```json
'{"color":"red","size":"M"}'
```

Example using `FOR JSON PATH`:

```sql
SELECT
    user_id                                      AS custom_id,
    'purchase'                                   AS event_name,
    inserted_at                                  AS last_updated_at,
    (SELECT brand, size FROM product_details
     WHERE id = p.id FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) AS product_details
FROM raw.purchases p;
```

***

#### 1.6 Example schema (e-commerce)

```sql
CREATE TABLE events_data.batch_events (
    custom_id          NVARCHAR(255),
    event_name         NVARCHAR(255),
    last_updated_at    DATETIME2,

    -- Optional event fields
    event_time         NVARCHAR(30),         -- RFC 3339 UTC, e.g. '2026-04-17T04:25:00Z'

    -- Standard event attributes
    item               NVARCHAR(255),
    quantity           INT,
    price              FLOAT,

    -- Typed event attributes
    url__item_url      NVARCHAR(2048),        -- URL attribute
    date__purchased_at NVARCHAR(10),          -- date attribute

    -- Object event attribute (stringified JSON)
    product_details    NVARCHAR(MAX)          -- e.g. '{"brand":"Nike","size":"42"}'
);
```

**How this maps in Batch:**

* `custom_id` identifies the profile the event is attached to
* `event_name` sets the event type
* `event_time` sets the event timestamp (falls back to reception time if omitted)
* `item`, `quantity`, `price` become standard event attributes
* `url__item_url` is interpreted as a URL attribute
* `date__purchased_at` is interpreted as a date attribute
* `product_details` is interpreted as an object attribute

***

#### 1.7 Using a View

If your raw event table doesn't match the expected naming or format, create an **MSSQL View** that converts your schema into the correct conventions.

```sql
CREATE OR ALTER VIEW events_data.batch_events_view AS
SELECT
    CAST(e.user_id AS NVARCHAR(255))                             AS custom_id,
    e.event_type                                                  AS event_name,
    e.inserted_at                                                 AS last_updated_at,

    FORMAT(e.occurred_at AT TIME ZONE 'UTC', 'yyyy-MM-ddTHH:mm:ssZ') AS event_time,

    e.item_name                                                   AS item,
    e.qty                                                         AS quantity,
    e.unit_price                                                  AS price,

    e.item_url                                                    AS url__item_url,
    CONVERT(NVARCHAR(10), e.purchase_date, 23)                    AS date__purchased_at,

    (SELECT e.brand, e.size FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) AS product_details
FROM raw.events e;
```

This approach lets you:

* rename fields with the correct prefixes (`date__`, `url__`)
* set a reliable `last_updated_at` based on insertion time
* format `event_time` to RFC 3339 UTC
* stringify object columns correctly

***

#### 1.8 Handling nulls

If an optional column value is `NULL`, that attribute is simply omitted from the event. It does not affect other attributes on the same event or on the profile.

***

### 2) Set up database access

Batch connects to your MSSQL database using either **username/password** or **Microsoft Entra ID** (recommended for Azure SQL).

#### Option A — Username / Password

Create a dedicated read-only user in your database and grant it `SELECT` access on the relevant table or view:

```sql
CREATE LOGIN batch_sync WITH PASSWORD = 'YourStrongPassword!';
CREATE USER batch_sync FOR LOGIN batch_sync;
GRANT SELECT ON events_data.batch_events_view TO batch_sync;
```

#### Option B — Microsoft Entra ID (recommended for Azure SQL)

This method uses a **service principal** (App Registration) to authenticate without storing a password.

**2.1 Create an App Registration in Entra ID**

1. Go to [entra.microsoft.com](https://entra.microsoft.com)
2. Navigate to **Entra ID → App registrations → New registration**
3. Give it a name (e.g. `Batch Cloud Sync`) and register it
4. Note the **Application (Client) ID** — you'll need it later
5. Go to **Certificates & secrets → New client secret**
6. Note the **Value** of the client secret — it is only shown once

**2.2 Create a database user for the service principal**

In your Azure SQL Database, run the following as an admin:

```sql
CREATE USER [Batch Cloud Sync] FROM EXTERNAL PROVIDER;
GRANT SELECT ON events_data.batch_events_view TO [Batch Cloud Sync];
```

{% hint style="warning" %}
The user name in brackets must match exactly the **display name** of your App Registration in Entra ID.
{% endhint %}

***

### 3) Create the Sync in the Batch dashboard

Cloud Sync is configured from the dashboard via a dedicated **Sync module**.

1. Open the **Batch dashboard**
2. Go to **Data → Cloud Sync**
3. Click **Create Sync**
4. Select **MSSQL** as the source

***

#### 3.1 Configure your MSSQL connection

Enter:

* **Host** — your server hostname (e.g. `myserver.database.windows.net` for Azure SQL, or `<workspace>.datawarehouse.fabric.microsoft.com` for Microsoft Fabric Warehouse)
* **Port** — default is `1433`
* **Database** — the name of your database
* **Schema** — the schema containing your table or view
* **Table or View** — the name of the source table or view

Then fill in your credentials:

* **Username / Password** if using SQL authentication, or
* **Entra ID Client ID** and **Entra ID Client Secret** if using Microsoft Entra ID

{% hint style="warning" %}
When using Microsoft Entra ID authentication, an encrypted connection is required. Make sure your server accepts encrypted connections.
{% endhint %}

Batch validates the connection before continuing.

***

#### 3.2 Select the destination

In the **Destination** dropdown, select **Batch > Profile events**.

***

#### 3.3 Configure event mapping

Cloud Sync applies a simple mapping model:

* `custom_id` → identifies which Batch profile to attach the event to
* `event_name` → sets the event type
* `last_updated_at` → used only for incremental sync logic
* all other columns → mapped to event attributes

***

### 4) How incremental sync works

Cloud Sync uses **incremental processing**, which means it does not re-import your full dataset at every run. Instead, it fetches only the rows that are new since the last successful sync.

***

#### 4.1 The `last_updated_at` cursor

Batch stores the last successful cursor value internally.

At each run, Batch fetches only rows where:

* `last_updated_at` is greater than the last stored cursor

This makes sync runs faster, more scalable, and more cost-efficient.

{% hint style="warning" %}
For events, `last_updated_at` should reflect when the row was **inserted** into the table, not when the event occurred (`event_time`). Do not backfill or modify `last_updated_at` after insertion.
{% endhint %}

***

#### 4.2 Inserts and deletes

Incremental syncs capture:

* ✅ new event rows (inserts)

They do **not** capture:

* ❌ deletions — events sent to Batch are immutable; they cannot be removed via Cloud Sync

***

#### 4.3 Best practices for reliable incremental syncs

To avoid missing events:

* Set `last_updated_at` at **insertion time** and never modify it afterwards
* Use a View if you need to rename columns or apply type conversions
* Index on `last_updated_at` for large tables

***

### 5) Test and enable your Sync

Before enabling the schedule:

1. Run a **test sync**
2. Verify:
   * Events are created on the correct profiles
   * `event_time` is set correctly (or defaults to reception time as expected)
   * `date__` and `url__` fields are interpreted correctly
   * Object attributes are correctly stringified

Once enabled, Batch automatically handles batching and retries.


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://doc.batch.com/getting-started/features/customer-engagement-platform/profiles/cloud-sync/create-a-sync-from-mssql-to-batch-profile-events.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.