Microsoft SQL Server

If your conversations already land in a SQL Server or Azure SQL Database table, you can send them to Dimension Labs from there. A scheduled job on your side queries the rows that are new since its last run and posts each message to the universal API.

What you need

• A SQL Server login that can create logins or users, and grant access to the table that holds your conversations.
• The server name and database, and network access to it from wherever the job runs. Azure SQL Database blocks all access to its public endpoint until you add a firewall rule for the job’s IP address (Azure SQL Database IP firewall rules).
• A Dimension Labs API key for a new Universal API source. See Your API key.

1. Get access

Create a SQL login and a database user for the job, and let it read only the conversations table. On SQL Server, create the login in master and the user in your database (CREATE LOGIN, CREATE USER):

CREATE LOGIN dl_sync WITH PASSWORD = 'choose-a-long-password';
-- then, in the database that holds your conversations:
CREATE USER dl_sync FOR LOGIN dl_sync;
GRANT SELECT ON support.conversation_messages TO dl_sync;

On Azure SQL Database you can skip the login and create a contained database user instead: CREATE USER dl_sync WITH PASSWORD = '...';. GRANT SELECT ON a table is described in GRANT object permissions.

Keep the password in a secrets manager, never in code you commit.

2. Query new rows

Use the mssql package, which runs on tedious, the JavaScript driver Microsoft lists for SQL Server (Node.js Driver for SQL Server). Each run asks for the rows changed since the last run, using updated_at as the cursor:

SELECT message_id, conversation_id, customer_id, sender_type, body, channel, sent_at, updated_at
FROM support.conversation_messages
WHERE updated_at >= @since
ORDER BY updated_at;

• @since is a named parameter, set with request.input('since', sql.DateTime2, value) (node-mssql). The code passes the cursor as a JavaScript Date.
• options.useUTC defaults to true, so datetime2 values without an offset are read and written as UTC (configuration). Store your times in UTC, or use datetimeoffset columns.
• requestTimeout defaults to 15 seconds. The code raises it to 5 minutes, so a long history query can finish.

3. Map the columns

This guide assumes a table with one row per message, with these columns. Rename them to match yours.

Column	Holds
message_id	The message’s own id
conversation_id	The conversation, ticket or chat the message belongs to
customer_id	Your internal id for the customer in that conversation, on every row, including agent and bot rows. Not an email address or phone number.
sender_type	Who wrote the message, for example customer, agent or bot
body	The message text (nvarchar)
channel	Chat, email, voice and so on
sent_at	When the message was sent (datetime2 in UTC, or datetimeoffset)
updated_at	When the row was written or last changed (same type)

Dimension Labs	SQL Server column	Notes
text	body	Skip rows where it is empty. Strip HTML if bodies are HTML.
userId	customer_id	If it is null, use the sessionId.
incoming	sender_type	true only when the customer wrote it. Agent and bot rows are false.
sessionId	conversation_id	Replace any colons.
conversationId	conversation_id	Same value as sessionId.
dimensionlabs_timestamp	sent_at	Pass the Date through toMillis.
platformJson	message_id, channel	Add any join keys you have, such as an account or store id.

Personal data. Don’t select email, phone or name columns. If your only customer key is an email, send a consistent hash of it instead. Message text can still hold personal details: redact them in the function before you send. See PII redaction.

4. Send it

Install the driver in your project first: npm install mssql.

// sql-server.mjs. Environment: MSSQL_SERVER (for example yourserver.database.windows.net), MSSQL_DATABASE, MSSQL_USER, MSSQL_PASSWORD, DL_API_KEY.
import sql from 'mssql';
import { sendToDimensionLabs, toMillis } from './dimensionlabs.mjs';

const HOUR = 60 * 60 * 1000;
const SQL = `SELECT message_id, conversation_id, customer_id, sender_type, body, channel, sent_at, updated_at
  FROM support.conversation_messages WHERE updated_at >= @since ORDER BY updated_at`;
const clean = (s) => String(s ?? '').replace(/<[^>]+>/g, ' ').replace(/\s+/g, ' ').trim();

// Sends every row written or changed at or after `since` (Unix ms). Returns the new `since`.
export async function syncSqlServer({ since, historical = false }) {
  const pool = await new sql.ConnectionPool({
    server: process.env.MSSQL_SERVER,
    database: process.env.MSSQL_DATABASE,
    user: process.env.MSSQL_USER,
    password: process.env.MSSQL_PASSWORD,
    requestTimeout: 5 * 60 * 1000,
    options: { encrypt: true, trustServerCertificate: false, useUTC: true },
  }).connect();
  try {
    const { recordset } = await pool.request()
      .input('since', sql.DateTime2, new Date(since))
      .query(SQL);
    let newest = since;
    for (const row of recordset) {
      const text = clean(row.body);
      const id = String(row.conversation_id).replaceAll(':', '-');
      if (text) {
        const ts = toMillis(row.sent_at);
        await sendToDimensionLabs({
          text,
          userId: row.customer_id ? String(row.customer_id).replaceAll(':', '-') : id,
          incoming: row.sender_type === 'customer',
          sessionId: id,
          conversationId: id,
          dimensionlabs_timestamp: ts,
          platformJson: { message_id: String(row.message_id), channel: row.channel },
        }, { historical: historical || ts < Date.now() - HOUR });
      }
      newest = Math.max(newest, toMillis(row.updated_at));
    }
    return newest;
  } finally {
    await pool.close();
  }
}

encrypt: true is the driver’s default. For a server with a self-signed certificate (a local test server, say), the driver’s docs say to set trustServerCertificate to true.

The cursor follows updated_at, so a row that changes later (a status column, say) is sent again. That’s fine: duplicates are removed automatically. An index on updated_at keeps the query fast as the table grows.

5. Run it

Save the code as sql-server.mjs in a project set up as in Set up your project, and run it with the scheduled sync runner: import { syncSqlServer as sync } from './sql-server.mjs';.

• History first: node run.mjs 2025-01-01 sends every row since that date to /trackhistorical. Then start a backfill from the Backfill tab.
• Then every 15 minutes: the scheduled job sends new rows to /track, and they are enriched automatically every day. A message whose sent_at is more than an hour old when the job sees it (a late-loaded batch, say) goes to /trackhistorical, and needs a backfill.
• Re-runs are safe: duplicates are removed automatically.
• No API rate limit: the database is the only limit. The query reads all new rows into memory at once. For a very large history, start from a recent date and upload the older rows as a file.

You can also export the rows to a CSV file and upload it. Run it through the file checker first.

SQL Server reference

• Node.js Driver for SQL Server
• node-mssql
• CREATE LOGIN
• CREATE USER
• GRANT object permissions
• Azure SQL Database IP firewall rules

Checked against Microsoft’s and node-mssql’s documentation on 2026-10-26.