SQL Bulk Copy

The SQL Bulk Copy activity copies all rows from a table in one SQL Server data source to a table in another (or the same) SQL Server data source using the high-performance SqlBulkCopy API.

Purpose

Use the SQL Bulk Copy activity to:

• Move large volumes of data between SQL Server databases without writing custom stored procedures
• Populate a staging table from a source database as part of a data pipeline
• Synchronise a table from one environment to another (e.g. from a data warehouse to the Una staging area)
• Clear and reload a reference table as part of a scheduled workflow

Configuration

Source

Field	Description
Source Data Source	The SQL Server data source to read from
Source Schema	Schema of the source table. Defaults to dbo if left blank. Supports interpolation
Source Table	Name of the source table (without schema). Supports interpolation

Destination

Field	Description
Destination Data Source	The SQL Server data source to write to. Can be the same as source or a different server
Destination Schema	Schema of the destination table. Defaults to dbo if left blank. Supports interpolation
Destination Table	Name of the destination table (without schema). Supports interpolation

Clear Table Data

When enabled, the destination table is truncated (TRUNCATE TABLE) before the copy begins. All existing rows are removed.

When disabled, rows are appended to any existing data in the destination table.

Behavior

1. Source and destination credentials are resolved from their respective data source configurations.
2. Schemas default to dbo if not specified. Table and schema names are validated to prevent SQL injection.
3. If Clear Table Data is enabled, the destination table is truncated first.
4. All rows are read from the source table via a SELECT * query.
5. Rows are written to the destination table in batches of 5,000 rows using SqlBulkCopy.
6. The bulk copy timeout is 3 minutes (fixed).
7. On success the activity returns Success. On any exception, the error is logged and the activity returns Error.

Column mapping is implicit — the source and destination tables must have the same column structure. SqlBulkCopy maps by column position, not by name.

Usage Patterns

Load Staging from External Source

SQL Bulk Copy
  Source:      ExternalWarehouse / dbo / FactActuals
  Destination: StagingDB / staging / Actuals
  Clear:       true
      │
      ▼
ETL ("Import_Actuals")

Sync a Reference Table

SQL Bulk Copy (scheduled, daily)
  Source:      MasterDataDB / ref / CostCentres
  Destination: AppDB / staging / CostCentres
  Clear:       true

Copy per Entity in a Loop

Foreach: entity in entities
  └──> SQL Bulk Copy
         Source Table: Actuals_{{foreach:item}}
         Destination:  StagingDB / staging / Actuals_{{foreach:item}}
         Clear:        true

Usage Notes

• Both source and destination must be SQL Server data sources. Other database types are not supported.
• The destination table must already exist. The activity does not create tables.
• Column order in source and destination must match — SqlBulkCopy maps by position, not by column name. If the schemas diverge, rows will be misaligned silently.
• TRUNCATE TABLE requires that the destination table has no foreign key constraints referencing it. If it does, the truncate will fail. Use a Stored Procedure activity to handle constraint-aware deletes instead.
• The bulk copy timeout is fixed at 3 minutes. For very large tables, consider splitting the load by partition or using a dedicated ETL pipeline.
• Schema and table names are validated to reject invalid characters. Names must contain only alphanumeric characters, underscores, and hyphens.

Comparison to ETL

Feature	SQL Bulk Copy	ETL
Source	Any SQL Server table	Una staging table
Destination	Any SQL Server table	Una model dimensions/facts
Column mapping	Positional (must match)	Configured per import definition
Transformation	None	Via import definition rules
Validation	None	Via import definition validation rules
Use case	Raw table-to-table copy	Structured model data load

Use SQL Bulk Copy when you need a fast, raw copy between database tables. Use ETL when you need column mapping, transformation, or validation before loading into the Una model.

Best Practices

• Always enable Clear Table Data unless you explicitly need to append. Silent accumulation of duplicate rows is a common source of data quality issues.
• Verify column order matches between source and destination before deploying to production. A schema change in either table will silently corrupt the copy.
• Add an error arrow to a Log Message or Email activity so bulk copy failures are surfaced immediately.
• For tables with millions of rows, run outside business hours and set appropriate timeouts on any upstream/downstream activities.

JSON Reference

{
  "discriminator": "SqlBulkCopyWorkflowActivity",
  "activityId": "<uuid>",
  "name": "SQL Bulk Copy",
  "positionX": 0,
  "positionY": 0,
  "advanceRule": 2,
  "sourceDataSource": "SourceDB",
  "destinationDataSource": "DestinationDB",
  "sourceSchema": "dbo",
  "sourceTable": "FactActuals",
  "destinationSchema": "staging",
  "destinationTable": "Actuals",
  "truncateDestinationTable": true
}

Property	Type	Description
sourceDataSource	string	Corresponds to the Source Data Source field. The SQL Server data source to read from.
destinationDataSource	string	Corresponds to the Destination Data Source field. The SQL Server data source to write to.
sourceSchema	string | null	Corresponds to the Source Schema field. Schema of the source table (defaults to dbo if null). Supports {variable} interpolation.
sourceTable	string	Corresponds to the Source Table field. Name of the source table without schema. Supports {variable} interpolation.
destinationSchema	string | null	Corresponds to the Destination Schema field. Schema of the destination table (defaults to dbo if null). Supports {variable} interpolation.
destinationTable	string	Corresponds to the Destination Table field. Name of the destination table without schema. Supports {variable} interpolation.
truncateDestinationTable	boolean	Corresponds to the Clear Table Data checkbox. When true, the destination table is truncated before the copy.