Microsoft SQL Server

The Conduit Platform by default supports Microsoft SQL Server as a source and a destination.

The Microsoft SQL Server source can connect to and emit records from a table.

Required Configurations

Name	Description	Required	Default
connection	Connection string for the SQL Server database. Example: sqlserver://username:[email protected]:1433/database	Yes
table	The table the source connector should read from.	Yes
primaryKey	Column name that records should use for their Key fields.	Yes	id
orderingColumn	The name of a column that the connector will use for ordering rows. The values must be unique and suitable for sorting, otherwise, the snapshot won't work correctly.	Yes	id

Looking for something else? See advanced configurations.

Initial Snapshot

Snapshot mode is enabled by default. When the source connector starts, the source connector retrieves the max value from orderingColumn and saves this value to position. The snapshot iterator reads all rows, where the orderingColumn value is less than or equal to maxValue, from the table in batches.

Note: The orderingColumn values must be unique and suitable for sorting, otherwise, the snapshot won't work correctly.

The snapshot iterator saves the last processed value from the orderingColumn column to position, specifically to the SnapshotLastProcessedVal field. If the snapshot process stops, it retrieves the position from the last record and attempts to fetch rows where orderingColumn is greater than position.SnapshotLastProcessedVal. Once all records are retrieved, the connector transitions to the CDC iterator.

Note: The default snapshot mode can be disabled by setting snapshot to false in the configuration.

Updates

The source connector utilizes Change Data Capture (CDC) for DB2 to detect changes in a Microsoft SQL Server table by creating a tracking table and trigger.

The tracking table and trigger will inherit the same names as the source table, prefixed with CONDUIT_TRACKING_. Additionally, the tracking table includes all columns from the source table along with the following additional columns:

Name	Description
CONDUIT_TRACKING_ID	Auto increment index for the position.
CONDUIT_OPERATION_TYPE	Operation type: INSERT, UPDATE, or DELETE.
CONDUIT_TRACKING_CREATED_DATE	Date when the event was added to the tacking table.

Triggers follow the naming pattern CONDUIT_TRIGGER_{{operation_type}}_{{table}}.

Queries for retrieving change data from a tracking table closely resemble those in a Snapshot iterator, with the only difference being the use of CONDUIT_TRACKING_ID as the ordering column.

The CDC iterator periodically clears rows that have been successfully applied from the tracking table. It gathers CONDUIT_TRACKING_ID values inside the Ack function into a batch and clears the tracking table every 5 seconds.

The CDC iterator saves the last CONDUIT_TRACKING_ID to the position from the last successfully recorded row.

If the source connector stops, it will parse the position from the last read record and attempt to retrieve rows where the CONDUIT_TRACKING_ID is greater than position.CDCLastID.

Advanced Configurations

Name	Description	Required	Default
column	Comma-separated list of column names that should be included in each Record's payload. If the field is not empty it must contain values of the primaryKey and orderingColumn fields. Example: id,name,email	No	all rows
snapshot	Enable or disable snapshot of entire table before starting CDC mode. Options: true or false.	No	true
batchSize	Size of rows batch. Min is 1 and max is 100000.	No	1000