Configuring Connection Details

For the direction and binder name of this Micro-Integration, see Micro-Integration for Microsoft SQL Server .

This section provides instructions for configuring the connection details required to establish communication between the Micro-Integration and your third-party system.

For information about configuring the connection to the event broker, see Step 1: Connecting to Your Event Broker .

For information about configuring error handling, see Step 5: Error Handling.

We recommend starting from the application.yml file provided in the samples/config directory of your downloaded archive, and updating it with the connection details described in this section, rather than building a configuration file from scratch. For more information, see Deploying Your Self-Managed Micro-Integration .

Before You Begin

  • 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.

To share settings across all workflows in a Micro-Integration, configure the binder-specific parameters under default at the binder level instead of under a workflow-specific identifier (for example, output-0) in bindings. This reduces repetition when multiple workflows use the same parameter values. Workflow-specific settings override default settings when both are present.

Note that default is a sibling of bindings; it is not nested under bindings. Use the same hierarchy under default as you use under bindings.<binding-name>. For example, for bindings.output-0.producer.someProperty=someValue the default setting is default.producer.someProperty=someValue.

spring:
  cloud:
    stream:
      <binder-name>:
        default:
          producer:
            <shared parameters>
        bindings:
          output-0:
            producer:
              <workflow-specific parameters>

Microsoft SQL Server Connection Details

Microsoft SQL Server supports SQL authentication (username and password). Configure the connection using the db.* properties in your application.yml file. For example:

db:
  hostname: sqlserver.example.com
  port-number: 1433
  database-name: mydatabase
  user: myuser
  password: mypassword
  encrypt: true
  trust-server-certificate: false

The following table lists the connection configuration options.

Property Type Valid Values Default Value Description

db.hostname

String

None

Required. The hostname of the SQL Server database server.

db.port-number

Integer

None

Required. The port of the SQL Server database server.

db.database-name

String

None

Required. The name of the SQL Server database.

db.user

String

None

Required. The SQL Server login username.

db.password

String

None

Required. The SQL Server login password.

db.encrypt

boolean

true, false

true

Optional. When true, indicates that the connection to SQL Server is encrypted using TLS.

db.trust-server-certificate

boolean

true, false

false

Optional. When true, indicates that the SQL Server certificate is trusted without validation. Use this option to accept self-signed certificates.

db.trust-store

String

None

Optional. The path to the trust store file used to validate the SQL Server certificate.

db.trust-store-password

String

None

Optional. The password for the trust store file.

db.host-name-in-certificate

String

None

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

Microsoft SQL Server Binder Configuration Options

The following properties are available at the Microsoft SQL Server binder level. The primary-key and operation properties are prefixed with spring.cloud.stream.sqlserver.bindings.<outputname>.producer.endpoint.query-parameters.. The destination property uses the standard Spring Cloud Stream binding property spring.cloud.stream.bindings.<outputname>.destination.

Microsoft SQL Server Producer Configuration Options

The following configuration options are available for the Microsoft SQL Server producers.

Property Type Valid Values Default Value Description

destination

String

None

Required. The target database table, in the format schema.table. For example, dbo.customers.

primary-key

String

id

Optional. The column name used as the primary key for the MERGE/INSERT operation. For composite keys, separate column names with commas, for example id, id_2.

operation

String

UPSERT, INSERT

UPSERT

Optional. Sets the write behavior for the target table.

UPSERT uses a native 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 scst_targetDestination header on the message. This header 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. For more information, see Dynamic Producer Destinations.

Null or absent fields in the incoming payload are omitted from the generated INSERT/MERGE statement rather than written as SQL NULL.

Connecting to Multiple Systems

To connect to multiple systems of the same type, use the multiple binder syntax.

For example:

spring:
  cloud:
    stream:
      binders:

        # 1st solace binder in this example
        solace1:
          type: solace
          environment:
            solace:
              java:
                host: tcp://localhost:55555

        # 2nd solace binder in this example
        solace2:
          type: solace
          environment:
            solace:
              java:
                host: tcp://other-host:55555

        # The only sqlserver binder
        sqlserver1:
          type: sqlserver
          # Add `environment` property map here if you need to customize this binder.
          # But for this example, we'll assume that defaults are used.

        # Required for internal use
        undefined:
          type: undefined
      bindings:
        input-0:
          destination: <input-destination>
          binder: solace1 # Reference 1st solace binder
        output-0:
          destination: <output-destination>
          binder: sqlserver1
        input-1:
          destination: <input-destination>
          binder: solace2 # Reference 2nd solace binder
        output-1:
          destination: <output-destination>
          binder: sqlserver1

The preceding configuration defines two binders of type solace and one binder of type sqlserver, which are then referenced within the bindings.

Each preceding binder is configured independently under spring.cloud.stream.binders.<bindername>.environment..

  • If you connect to multiple systems, all binder configuration must be specified using the multiple binder syntax for all binders. For example, under the spring.cloud.stream.binders.<binder-name>.environment.

  • Do not use single-binder configuration (for example, solace.java.* at the root of your application.yml) while using the multiple binder syntax.