Skip to content

SQL Server Connector ​

The NPipeline.Connectors.SqlServer package reads SQL Server queries into records and writes records to tables, with multi-row INSERT statements, one statement per row, or SqlBulkCopy, and upserts with MERGE. Mapping, row errors, transactions, failed batches and checkpoints work as in every SQL connector; see SQL Connectors: Shared Behaviour.

Installation ​

bash
dotnet add package NPipeline.Connectors.SqlServer

Dependencies: Microsoft.Data.SqlClient, NPipeline.Connectors

Reading ​

csharp
public sealed record Order(int Id, string Customer, decimal Total, DateTimeOffset PlacedAt);

var source = SqlServerConnector.Source<Order>(connectionString, "SELECT Id, Customer, Total, PlacedAt FROM dbo.Orders ORDER BY Id");
builder.AddSource(source, "orders");

Columns bind to members by name, case-insensitively, and members map to columns of the same name. Parameters are bound by name:

csharp
var recent = SqlServerConnector.Source<Order>(connectionString, "SELECT * FROM dbo.Orders WHERE PlacedAt >= @since ORDER BY Id",
    o => o with { Parameters = [new DatabaseParameter("@since", DateTimeOffset.UtcNow.AddDays(-1))] });

Writing ​

csharp
var sink = SqlServerConnector.Sink<Order>(connectionString, "Orders", o => o with { Schema = "sales" });
builder.AddSink(sink, "orders-out");
WriteStrategyWritesNotes
Batch (default)Multi-row INSERT or MERGE statementsSplit to stay under SQL Server's 2,100-parameter limit
PerRowOne statement per rowSlowest; one command, reused
BulkCopySqlBulkCopy, streaming the batch through a data readerFastest for large loads; inserts only

Parameters are typed so every statement reuses one query plan: strings as nvarchar(4000) (or nvarchar(max) when longer), binary as varbinary(8000), decimals as decimal(38, 18) (a value that does not fit is typed by itself), and dates as datetime2, datetimeoffset, date and time. Batch statements carry at most ten rows, which SQL Server compiles fastest.

Upserts ​

Upsert = SqlUpsert.On("Id") writes MERGE … WITH (HOLDLOCK), which makes the match and the insert one atomic step, so concurrent upserts of a key cannot both insert it. SqlUpsertAction.Ignore inserts only rows whose key is new.

Write options ​

OptionDefaultDescription
WriteStrategyBatchSee above
BulkCopyTimeout30 sThe SqlBulkCopy timeout
ResilienceSqlServerConnectorResilience.DefaultRetries a batch after a transient failure; see Resilience

Plus the options every SQL sink has.

Attributes ​

[Column] and [IgnoreColumn] from NPipeline.Connectors.Attributes work as in every connector. [SqlServerColumn] adds:

csharp
public sealed class Customer
{
    [SqlServerColumn("CustomerID", Identity = true)]
    public int CustomerId { get; set; }

    [SqlServerColumn("Code", DbType = SqlDbType.VarChar, Size = 10)]
    public string Code { get; set; } = "";
}
PropertyDescription
Name, IgnoreAs [Column]
IdentityThe column is generated by the database: read, but never written
DbType, SizeThe parameter's SqlDbType and size, instead of the connector's choice

A sink writes every readable member, so mark computed and generated members [IgnoreColumn]; see Which members are written.

Connections ​

A source or sink takes a connection string, an mssql:// storage URI, or an ISqlServerConnectionPool of named connection strings (set ConnectionName to pick one). Connection strings are used as they are, so pool sizes, timeouts and encryption belong in them:

  • Encrypt is True, False, Strict or Optional; keep TrustServerCertificate=False outside development.
  • Application Name=orders-pipeline identifies the pipeline's sessions in Activity Monitor, sys.dm_exec_sessions and Extended Events.

mssql:// URIs are described in Connections.

Resilience ​

With SqlTransactionMode.PerBatch (the default), the sink retries a batch that failed transiently with NResilience. The batch's transaction was rolled back, so a retry never writes a row twice. SqlServerConnectorResilience.Default:

  • Makes up to four attempts (three retries).
  • Retries timeouts (-2), network errors (53, 64, 121), deadlocks (1205), and Azure SQL unavailability (40613).
  • Treats Azure SQL throttling (40501, 10928, 10929, and 49918-49920) as throttling, which waits longer: backoff starts at 10 seconds.
  • Doesn't retry other errors, such as a constraint violation or a missing table.
  • Waits with exponential backoff and full jitter, from 1 second up to 30 seconds.
  • Has no attempt timeout and no deadline. The driver's timeout bounds each attempt: CommandTimeout, or BulkCopyTimeout for bulk copy, so a long bulk load isn't cut off by a retry policy.

A connection that a failure closed is reopened before the retry. To change a setting, derive a policy: o => o with { Resilience = SqlServerConnectorResilience.Default with { Attempts = 6 } }; Resilience.None turns retries off. Leave SqlClient's own retry logic (SqlConfigurableRetryFactory) off, because two retrying layers multiply attempts.

Dependency Injection ​

csharp
services.AddSqlServerConnector(options => options.DefaultConnectionString = "Server=localhost;Database=Sales;...");
services.AddSqlServerConnection("reporting", "Server=reporting-db;...");

Registers ISqlServerConnectionPool, ISqlServerSourceNodeFactory and ISqlServerSinkNodeFactory:

csharp
var source = sources.CreateSourceNode<Order>("SELECT * FROM dbo.Orders ORDER BY Id", o => o with { ConnectionName = "reporting" });

Analyzer ​

The connector package includes a build-time analyzer that reports NP9502 when a source checkpoints (CheckpointStrategy.Offset or InMemory) with a query that has no ORDER BY; see Checkpoints.

Next Steps ​

Released under the MIT License.