MsSQL Insert Documentation

Version: 3.0.0 

Retrieved: 2025-10-09 15:16:06

Microsoft SQL Server Insert

This module inserts flow message data into Microsoft SQL Server database row(s).

Settings

Credential

This module needs a credential of type 'Connection string' to connect to the database server. A minimal connection string is shown below. Depending on the setup of your database server additional parameters may be needed. Please consult the documentation for your server.

Server=tcp:192.168.0.5;Database=crosser;User ID=demo;Password=crosser123;

NOTE: From version 2.0.0 the default off the Encrypt parameter has changed from false to true, if you rely on it being set to false then you need to specify it

Server=tcp:192.168.0.5;Database=crosser;User ID=demo;Password=crosser123;Encrypt=false;

To set a custom command timeout (in seconds) for SQL operations:

Server=tcp:192.168.0.5;Database=crosser;User ID=demo;Password=crosser123;Encrypt=false;Command Timeout=60

Property Mapping

The module supports flexible property mapping to transform source data before inserting into the database:

Source Property

• Purpose: Locates data within the FlowMessage
• Single Object: Points to an object containing the data to insert
• Array/Batch: Points to an array of objects for batch insertion
• Default: If empty, uses the entire message as source

Column Mappings

• Purpose: Maps properties from source objects to database columns
• Transformation: Applied to each object found at the Source Property location
• Nested Properties: Supports dot notation (e.g., profile.email, user.details.name)
• Auto-mapping: If no column mapping is defined, the column names will be used as property names.

How They Work Together

1. Source Property locates the data (single object or array)
2. Column Mappings transform each object by mapping properties to columns
3. For batch inserts, mappings are applied to every object in the array

Input

The module supports both single row and batch inserts with flexible property mapping:

Output

An output message will be sent each time the module receives a message.

Examples

Example 1: Simple Insert with Default Mapping

Inserts a single row into the table sensors. Properties map 1:1 with column names:

# Settings
Source Property: sensorData
Table Name: sensors
Column Mappings: [] (empty - uses 1:1 mapping)

# Input
{
  "id": 1,
  "name": "machine",
  "sensorData": {
    "name": "sensor1",
    "reading": 489,
    "timestamp": "2024-01-15T10:30:00Z"
  }
}

# Database columns: name, reading, timestamp

Example 2: Single Insert with Property Mapping

Maps source properties to different column names:

# Settings
Source Property: sensor
Table Name: measurements
Column Mappings: [
  { "ColumnName": "device_name", "SourceProperty": "name" },
  { "ColumnName": "value", "SourceProperty": "reading" },
  { "ColumnName": "recorded_at", "SourceProperty": "timestamp" }
]

# Input
{
  "id": 1,
  "sensor": {
    "name": "temperature_sensor",
    "reading": 23.5,
    "timestamp": "2024-01-15T10:30:00Z"
  }
}

# Result: Maps name→device_name, reading→value, timestamp→recorded_at

Example 3: Batch Insert with Nested Property Mapping

Inserts multiple rows with complex property mapping:

# Settings
Source Property: batch.users
Table Name: user_profiles
Column Mappings: [
  { "ColumnName": "user_id", "SourceProperty": "id" },
  { "ColumnName": "full_name", "SourceProperty": "profile.displayName" },
  { "ColumnName": "email_address", "SourceProperty": "contact.email" },
  { "ColumnName": "created_date", "SourceProperty": "metadata.createdAt" }
]

# Input
{
  "batch": {
    "users": [
      {
        "id": 1,
        "profile": { "displayName": "John Doe" },
        "contact": { "email": "john@example.com" },
        "metadata": { "createdAt": "2024-01-15T10:00:00Z" }
      },
      {
        "id": 2,
        "profile": { "displayName": "Jane Smith" },
        "contact": { "email": "jane@example.com" },
        "metadata": { "createdAt": "2024-01-15T10:01:00Z" }
      }
    ]
  }
}

# Result: Inserts 2 rows, each with mapped properties

Example 4: Mixed Property Mapping

Combines direct and nested property mapping:

# Settings
Source Property: data
Table Name: orders
Column Mappings: [
  { "ColumnName": "order_id", "SourceProperty": "id" },
  { "ColumnName": "customer_name", "SourceProperty": "customer.name" },
  { "ColumnName": "total_amount", "SourceProperty": "pricing.total" },
  { "ColumnName": "status", "SourceProperty": "" }  // Uses column name as property
]

# Input
{
  "data": {
    "id": "ORD-001",
    "customer": { "name": "ACME Corp" },
    "pricing": { "total": 1250.00 },
    "status": "pending"
  }
}

# Result: Maps id→order_id, customer.name→customer_name, pricing.total→total_amount, status→status