Microsoft SQL Server™

Connect to and query Microsoft SQL Server™ (MSSQL) databases with OIBus.

This connector is commonly used to extract process and historian data from industrial platforms that store their data in SQL Server, such as AVEVA™ Historian (formerly Wonderware® InSQL) or Inductive Automation Ignition®.

Compatibility Notes

• Supports SQL Server 2012 and later
• Requires TCP/IP protocol to be enabled on SQL Server

Specific Settings

Setting	Description	Example Value
Host	IP address or hostname of the SQL Server instance.	192.168.1.10
Port	SQL Server port number. Default: 1433.	1433
Connection timeout	Maximum time in milliseconds to establish connection. Default: 5000.	5000
Database	Name of the database to connect to.	production
Use encryption	Enable data encryption between OIBus and the database.	Enabled/Disabled
Trust server certificate	Accept self-signed or expired certificates. Use with caution.	Enabled/Disabled
Username	Authentication username.	sa
Password	Authentication password.	••••••••
Domain	Active Directory domain for domain authentication (optional).	CORP
Request timeout	Maximum execution time in milliseconds for individual SQL queries.	15000

Security Best Practices

• User Permissions: Always use a read-only database user
• Certificate Validation: Only trust server certificates in development/test environments
• Encryption: Enable for sensitive data, untrusted networks, or compliance requirements

Performance Considerations

Query Optimization:

• Use indexed columns in WHERE clauses
• Limit result sets (avoid SELECT *)
• Prefer parameterized queries

Resource Management:

• Encryption adds CPU overhead
• Increase timeouts for long-running queries
• Implement pagination for large result sets
• Reuse connections where possible

Group Settings

Items can be organised into groups. Each group defines a shared collection schedule and default throttling settings. Items in the same group are still fetched one at a time in sequence — the group simply provides common defaults that individual items can override.

Setting	Description	Example Value
Name	Unique label for the group within this connector.	Group A
Scan mode	Schedule used to collect all items in the group.	Every 1 min
Throttling	Default throttling values (Max read interval, Read delay, Overlap) inherited by items in the group.	3600, 200, 0

Item Settings

Each item can be individually configured with its own query parameters and datetime handling. Items inherit their scan mode and throttling defaults from their group, but each setting can be overridden per item by disabling Sync with group.

Throttling Settings

Throttling controls how OIBus paces historical data requests. These settings appear on each group (for connectors that support groups) or on each item (for single-item connectors). Items in a group can override the group defaults by disabling the Sync with group toggle.

Setting	Description	Example Value
Max read interval	Maximum duration of each sub-query in seconds. Larger time ranges are automatically split into chunks not exceeding this value.	3600
Read delay	Pause in milliseconds between consecutive sub-queries. Helps prevent server overload and manages rate limits.	1000
Overlap	Time in milliseconds subtracted from @StartTime of each query to capture late-arriving data from the previous interval.	60000

How Throttling Works

1. Interval splitting — A 24-hour range with Max read interval = 3600 (1 hour) is split into 24 separate 1-hour sub-queries.
2. Read delay — A pause is inserted between sub-queries to manage server load.
3. Overlap — With Overlap = 60000 (1 minute), a query for [10:00–11:00] actually requests [9:59–11:00], ensuring no late-arriving data is missed.

Overlap applies to the full query range

The overlap value is subtracted from the start of the overall query, not from the start of each individual sub-interval when a large range is split into chunks.

Recommended Configurations

Scenario	Max read interval	Read delay	Overlap
Stable network, small datasets	3600 (1 hour)	500	0 (no overlap)
Unstable network	1800 (30 min)	2000	0 (no overlap)
Large historical retrievals	7200 (2 hours)	1000	0 (no overlap)
Real-time with occasional gaps	900 (15 min)	200	15000 (15 sec)

Query Configuration

The query field accepts standard SQL syntax with support for internal variables that enhance data retrieval resilience and performance optimization.

Query Variables

Setting	Description	Example Value
@StartTime	Initialized to first execution time, then updated to the most recent timestamp from reference field	2024-01-15T10:00:00.000Z
@EndTime	Set to current time (now()) or sub-interval end when queries are split	2024-01-15T11:00:00.000Z

Example Query:

SELECT device_id, value, reading_time
FROM sensor_data
WHERE reading_time > @StartTime
AND reading_time < @EndTime
ORDER BY reading_time

Query Splitting

For handling large datasets, queries can be automatically divided into smaller time-based chunks based on the Max read interval throttling setting.

Datetime Field Configuration

Setting	Description	Example Value
Field name	Name of the datetime field in your SELECT statement	timestamp, reading_time
Reference field	Designates which field determines the @StartTime for subsequent queries	reading_time
Type	Data type of the datetime field. Available values differ by connector — see the connector-specific note below.	iso-string, unix-epoch
Timezone	Timezone of the stored datetime (for string/date types)	UTC, Europe/Paris
Format	Format pattern for string-based datetimes	yyyy-MM-dd HH:mm:ss
Locale	Locale for format elements (e.g., month names)	en_US, fr_FR

Type Consistency Requirement

When using datetime conversions in queries, keep the type consistent in both the SELECT list and the WHERE clause:

Problematic approach (may cause unexpected behavior):

SELECT value, CONVERT(datetime, string_field) AS timestamp
FROM table
WHERE string_field > @StartTime

Recommended approach:

SELECT value, CONVERT(datetime, string_field) AS timestamp
FROM table
WHERE CONVERT(datetime, string_field) > @StartTime

Data Flow Process

1. OIBus executes each configured query sequentially.
2. Results are consolidated into structured output.
3. The max instant is extracted from the reference datetime field, converted to UTC, and stored as the next @StartTime.
4. Final output is formatted according to serialization settings.
5. Processed data is sent to configured North connectors.

Best Practices

1. Choose reference fields with consistent, increasing values.
2. For string datetimes, ensure the format matches between database storage and query variables.
3. Add indexes on datetime fields for better query performance.
4. Use the Max read interval to split large time ranges into manageable chunks.

CSV Serialization

OIBus provides flexible options for serializing retrieved data into CSV format with customizable output settings.

File Configuration

Setting	Description	Example Value
Filename	Name pattern for output files.	data_@ConnectorName_@CurrentDate.csv
Delimiter	Character used to separate values in the CSV file	COMMA (,), SEMI_COLON (;), DOT (.), COLON (:), PIPE (|), SLASH (/), TAB (\t), NON_BREAKING_SPACE
Compression	Enable gzip compression for output files	Enabled/Disabled

Filename Variables

The following variables can be used in filename patterns:

• @ConnectorName: Automatically replaced with the connector's name
• @CurrentDate: Inserts current timestamp in fixed yyyy_MM_dd_HH_mm_ss_SSS format

Temporal Data Handling

Setting	Description	Example Value
Output datetime format	Format pattern for datetime fields in the CSV (does not affect @CurrentDate in filenames)	yyyy-MM-dd HH:mm:ss
Output timezone	Timezone used for datetime values in the CSV	UTC or Europe/Paris

Important Notes

1. The @CurrentDate variable in filenames uses a fixed format (yyyy_MM_dd_HH_mm_ss_SSS) regardless of the datetime format setting
2. Only datetime fields specified in your configuration will be formatted according to these settings
3. Timezone conversion only applies to datetime values in the CSV content, not to the filename timestamp

MSSQL datetime field types

The following Type values are available for datetime field configuration in Microsoft SQL Server:

Type	Description
String	String representation parsed with a custom format
Date	SQL DATE column (date only, no time)
DateTime	SQL DATETIME column
DateTime2	SQL DATETIME2 column (higher precision)
DateTime Offset	SQL DATETIMEOFFSET column (includes timezone offset)
Small DateTime	SQL SMALLDATETIME column (minute precision)
ISO String	ISO 8601 string (e.g. 2024-01-15T10:30:00.000Z)
UNIX epoch (s)	Unix timestamp in seconds
UNIX epoch (ms)	Unix timestamp in milliseconds