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
dotnet add package NPipeline.Connectors.SqlServerDependencies: Microsoft.Data.SqlClient, NPipeline.Connectors
Reading
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:
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
var sink = SqlServerConnector.Sink<Order>(connectionString, "Orders", o => o with { Schema = "sales" });
builder.AddSink(sink, "orders-out");WriteStrategy | Writes | Notes |
|---|---|---|
Batch (default) | Multi-row INSERT or MERGE statements | Split to stay under SQL Server's 2,100-parameter limit |
PerRow | One statement per row | Slowest; one command, reused |
BulkCopy | SqlBulkCopy, streaming the batch through a data reader | Fastest 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
| Option | Default | Description |
|---|---|---|
WriteStrategy | Batch | See above |
BulkCopyTimeout | 30 s | The SqlBulkCopy timeout |
Resilience | SqlServerConnectorResilience.Default | Retries 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:
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; } = "";
}| Property | Description |
|---|---|
Name, Ignore | As [Column] |
Identity | The column is generated by the database: read, but never written |
DbType, Size | The 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:
EncryptisTrue,False,StrictorOptional; keepTrustServerCertificate=Falseoutside development.Application Name=orders-pipelineidentifies the pipeline's sessions in Activity Monitor,sys.dm_exec_sessionsand 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, orBulkCopyTimeoutfor 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
services.AddSqlServerConnector(options => options.DefaultConnectionString = "Server=localhost;Database=Sales;...");
services.AddSqlServerConnection("reporting", "Server=reporting-db;...");Registers ISqlServerConnectionPool, ISqlServerSourceNodeFactory and ISqlServerSinkNodeFactory:
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
- SQL Connectors: Shared Behaviour: mapping, row errors, transactions, upserts, checkpoints
- PostgreSQL Connector
- Storage Providers:
mssql://URIs
