Microsoft SQL Server

You can configure Microsoft SQL Server only as a target.

To configure the connection details for Microsoft SQL Server in your Micro-Integration, see Microsoft SQL Server Connection Parameters.

You must also define at least one Micro-Integration Flow that specifies:

For a listing of available message headers, see Microsoft SQL Server Message Headers.

Before You Begin

Before you configure the Microsoft SQL Server Micro-Integration, ensure you meet these requirements:

  • The target Microsoft SQL Server table must already exist. This Micro-Integration does not automatically create schemas or tables; it reads the target table's column metadata at startup.

  • The target table must have a primary key that matches the column or columns you configure as the primary key for the Micro-Integration. For composite keys, this is a comma-separated list of column names.

  • The database user specified in the connection configuration must have SELECT privileges (to read column metadata), and INSERT and UPDATE privileges (both are required for the default UPSERT operation, which uses a SQL MERGE statement) on the target table.

  • Message payloads must be JSON. The Micro-Integration does not support XML payloads for Microsoft SQL Server targets.

Microsoft SQL Server Connection Parameters

The following table describes the connection parameters for Microsoft SQL Server.

Field Description

Hostname

The hostname of the Microsoft SQL Server database server.

Port Number

The port of the Microsoft SQL Server database server. The default is 1433.

Database Name

The name of the Microsoft SQL Server database.

Username

The Microsoft SQL Server login username.

Password

The Microsoft SQL Server login password.

Encrypt

Select to encrypt the connection to Microsoft SQL Server using TLS.

Trust Server Certificate

Select to trust the Microsoft SQL Server certificate without validation.

Trust Store

(Optional) The trust store file used to validate the Microsoft SQL Server certificate.

Trust Store Password

(Optional) The password for the trust store file.

Hostname in Certificate

(Optional) The expected hostname in the server's TLS certificate. Use this option when the certificate hostname differs from the connection hostname.

Micro-Integration Flow Parameters

You must configure the endpoint parameters for each Flow. Each Flow can have different settings, but they all share the connection details of the parent Micro-Integration.

Microsoft SQL Server Target Parameters

The following table describes the parameters for configuring Microsoft SQL Server as a target.

Field Description

Destination

The target database table, including the schema name. Specify the destination using the <schemaName>.<tableName> format. For example, dbo.customers.

Primary Key

One or more column names used as the primary key for the MERGE/INSERT operation. For composite keys, separate column names with commas. The default is id.
Operation Mode

The write behavior for the database. Choose one of:

  • UPSERT (default)—Uses a native Microsoft SQL Server MERGE INTO statement, keyed on Primary Key, to update existing records or insert new ones.

  • INSERT—Always inserts a new row and relies on database constraints to reject duplicates.

You can override the configured Destination and Primary Key at runtime by setting the Smart Destination field on the Mapping step. This field always requires all three parts together, in the format schema.table.primaryKey (comma-separated for composite keys)—you cannot override just the destination or just the primary key on their own.

Troubleshooting

The following troubleshooting tips might help you resolve issues with this Micro-Integration. If problems persist, contact Solace.

Connection and Authentication Errors

If the Micro-Integration fails to connect to the target Microsoft SQL Server database:

  • Verify that the Hostname, Port, Database Name, Username, and Password connection parameters are correct.

  • If you connect over TLS, verify that the Encrypt Connection setting matches your server's TLS configuration. If your server uses a self-signed certificate, enable Trust Server Certificate, or provide a trust store for custom certificate authority validation.

  • Confirm that the database user has SELECT privileges on the target table. The Micro-Integration reads column metadata from the target table at startup and fails to start if it cannot.

Destination Not Found or Misconfigured Errors

If the Micro-Integration logs errors indicating that the target table or destination cannot be found:

  • Verify that the destination is in the format schema.table (for example, dbo.customers) and that the table already exists. The Micro-Integration does not create tables automatically.

  • If you use dynamic destinations, verify that the scst_targetDestination header value uses the dot-separated format schema.table.primaryKey, with a comma-separated list of column names if the primary key is composite.

Write Failures

If the Micro-Integration logs errors while writing to the target table:

  • Verify that the configured primary key column or columns exist in the target table and match its actual primary key.

  • Confirm that the database user has INSERT and UPDATE privileges on the target table. The default UPSERT operation uses a SQL MERGE statement, which requires both.

  • Check that the message payload is valid JSON. The Micro-Integration does not support XML payloads for Microsoft SQL Server targets.

Error Output and Logging

Review the Micro-Integration logs for specific error messages, such as connection errors, SQL exceptions, or authentication failures.