Class AdbSqlClientBulkInsertTarget<TInput>

A dataflow worker that inserts all incoming records into a SQL Server® table or non-partitioned view using SqlBulkCopy, which achieves high performance by loading records in bulk. Also see SqlBulkCopyColumnOrderHintCollection as well as Optimizing Bulk Import Performance.

Enabling TableLock improves performance significantly, but prohibits any other thread or process to access the table while the bulk copy is running.

Note that this worker requires an AdbSqlClientProvider connection, see SQL Database Access for details.

Note: Use the factory methods in AdbSqlClientBulkInsertTargetFactory to create instances of this class.

Note: This worker does participate in any transaction currently active on the connection.

Inheritance
Object
WorkerParent
WorkerBase
WorkerBase<AdbSqlClientBulkInsertTarget<TInput>>
AdbSqlClientBulkInsertTarget<TInput>
Implements
Inherited Members
Namespace: actionETL.Adb.SqlClientExternal
Assembly: actionETL.dll
Syntax
public class AdbSqlClientBulkInsertTarget<TInput> : WorkerBase<AdbSqlClientBulkInsertTarget<TInput>>, IDisposeOnFinished where TInput : class
Type Parameters
Name Description
TInput

The type of each Input row.

Properties

BatchSize

Gets or sets the maximum number of rows in each batch sent to the database.

Note that the actual number of rows in each batch is also affected by how quickly the upstream worker is sending rows.

Cannot be set after the worker has started running.

Note: This property is thread-safe.

Declaration
public int BatchSize { get; set; }
Property Value
Type Description
Int32

Number of rows in each batch, or 0 (the default) to send up to all available rows in one batch.

Note that the default unlimited or a very large batch size can consume a large amount of resources on the database until all batches have been written.

Note that even when set to 0, multiple batches will often be used since this worker will sometimes consume rows faster than upstream produces them. Increasing RowsPerBuffer can assist with sending larger batches to the server.

Note that if enabling BulkCopyTimeout with too short a duration, using a large or unlimited batch size can cause timeouts.

Exceptions
Type Condition
InvalidOperationException

Cannot set the value after the worker has started running.

BulkCopyTimeout

Gets or sets the database timeout in seconds. Cannot be set after the worker has started running.

Note: This property is thread-safe.

Declaration
public int BulkCopyTimeout { get; set; }
Property Value
Type Description
Int32

The bulk copy timeout. Set to 0 to disable the timeout (the default).

Note that using unlimited (the default) or a large BatchSize together with too short a timeout duration can cause timeouts.

Exceptions
Type Condition
InvalidOperationException

Cannot set the value after the worker has started running.

ColumnOrderHints

Gets the SQL bulk column order hint collection.

Using this can increase performance when the dataflow rows are already sorted and you are inserting into a table with a clustered index with the same sort order.

Note: Only one thread can add hints to this collection.

Declaration
public SqlBulkCopyColumnOrderHintCollection ColumnOrderHints { get; }
Property Value
Type Description
SqlBulkCopyColumnOrderHintCollection

CompositeTableName

Gets or sets the composite name of the table to insert into. The string must have a format appropriate for the data source. E.g. for the SqlClient provider: Product, dbo.Product, [Sales].[dbo].[Internet Sales], or "Sales"."dbo"."Internet Sales". Cannot be set after the worker has started running.

Note: This property is thread-safe.

Declaration
public string CompositeTableName { get; set; }
Property Value
Type Description
String

The composite name of the table.
Exceptions
Type Condition
InvalidOperationException

Cannot set the value after the worker has started running.

ConnectionBuilder

Gets or sets the database connection builder. Cannot be set after the worker has started running.

Note: This property is thread-safe.

Declaration
public IAdbConnectionBuilder ConnectionBuilder { get; set; }
Property Value
Type Description
IAdbConnectionBuilder
Exceptions
Type Condition
InvalidOperationException

Cannot set the value after the worker has started running.

CopyOptions

Gets or sets the SQL bulk copy options. Cannot be set after the worker has started running.

Note: This property is thread-safe.

Declaration
public SqlBulkCopyOptions CopyOptions { get; set; }
Property Value
Type Description
SqlBulkCopyOptions
Exceptions
Type Condition
InvalidOperationException

Cannot set the value after the worker has started running.

Input

Gets the input port for receiving rows from an upstream worker.

Declaration
public InputPort<TInput> Input { get; }
Property Value
Type Description
InputPort<TInput>

Methods

RunAsync()

This method can be overridden to add custom functionality to the derived worker that runs before and after the row processing. In this case, the base class base.RunAsync() must be called for the worker to function correctly.

Typically, this worker is used without overriding this method.

Declaration
protected override async Task<OutcomeStatus> RunAsync()
Returns
Type Description
Task<OutcomeStatus>

A Task describing the success or failure of the worker. An asynchronous async implementation would e.g. return OutcomeStatus.Succeeded on success, while a synchronous implementation would return OutcomeStatus.SucceededTask.
Overrides

Implements

See Also