DataTongs for SQL Server

From the directory containing your DataTongs.settings.json:

DataTongs

With a custom config file:

DataTongs --ConfigFile:C:\configs\extract-reference-data.json

With a full SQL Server connection string override:

DataTongs --ConnectionString:"Data Source=myserver;Initial Catalog=AppMain;User ID=sa;Password=secret;TrustServerCertificate=True;"

DataTongs connects to the source database, reads every table listed in the Tables array, serializes each row to JSON in deterministic key order, and writes a sync script plus a sibling .tabledata file for each table. Progress is logged to DataTongs - Progress.log and any errors to DataTongs - Errors.log in the working directory.

Table name

Specify tables in schema.table format. If you omit the schema, dbo is assumed:

{ "Name": "dbo.Country" }
{ "Name": "Products" }
{ "Name": "HumanResources.Department" }

DataTongs validates that each table exists in the source database before attempting extraction. Tables that don't exist are skipped with an error message.

Key columns

Key columns define the row-matching predicate — the ON clause of the generated MERGE.

Auto-detection. When KeyColumns is blank, DataTongs queries the table's indexes and selects the primary key if one exists. If there's no primary key, it falls back to the first available unique index. Nullable columns in the detected key are automatically prefixed with * for NULL-safe comparison. This handles the vast majority of tables without any manual configuration.

Manual override. When you specify KeyColumns, DataTongs uses your list instead of auto-detection. Separate multiple columns with commas:

{ "Name": "dbo.OrderLine", "KeyColumns": "OrderID, LineNumber" }

Nullable key columns. If a key column allows NULLs, prefix it with *. This generates NULL-safe matching:

{ "Name": "dbo.Mapping", "KeyColumns": "SourceCode, *TargetCode" }

The generated ON clause becomes:

ON Source.[SourceCode] = Target.[SourceCode]
   AND (Source.[TargetCode] = Target.[TargetCode]
        OR (Source.[TargetCode] IS NULL AND Target.[TargetCode] IS NULL))

When auto-detection discovers a nullable column in a unique index, it automatically applies the * behavior.

No key available. If a table has no primary key, no unique index, and no KeyColumns configured, DataTongs skips the table with an error message.

Filter

The Filter field accepts a SQL WHERE clause (without the WHERE keyword). It controls two things:

1. Which rows are extracted from the source database.
2. Which target rows are eligible for deletion when MergeDelete is enabled.

{ "Name": "dbo.FeatureFlags", "KeyColumns": "FlagName", "Filter": "IsActive = 1" }

This extracts only active flags and, if MergeDelete is on, only deletes active flags that no longer exist in the source. Inactive flags in the target are untouched.

What DataTongs writes to disk on SQL Server is a single MERGE statement with the source data embedded as a JSON literal and parsed through OPENJSON. The script is self-contained — no staging tables, no external files, no bulk-load dependencies. Run it against any compatible database and the data arrives.

MERGE with OPENJSON

DECLARE @v_json NVARCHAR(MAX) = '[
{"CompanyName":"Speedy Express","Phone":"(503) 555-9831","ShipperID":1},
{"CompanyName":"United Package","Phone":"(503) 555-3199","ShipperID":2},
{"CompanyName":"Federal Shipping","Phone":"(503) 555-9931","ShipperID":3}
]';

ALTER TABLE [dbo].[Shippers] DISABLE TRIGGER ALL;     -- if DisableTriggers
SET IDENTITY_INSERT [dbo].[Shippers] ON;              -- if identity column exists

MERGE INTO [dbo].[Shippers] AS Target
USING (
  SELECT [CompanyName],[Phone],[ShipperID]
    FROM OPENJSON(@v_json)
    WITH (
      [CompanyName] NVARCHAR(40),
      [Phone] NVARCHAR(24),
      [ShipperID] INT
    )
) AS Source
ON Source.[ShipperID] = Target.[ShipperID]

WHEN MATCHED AND (<change detection>) THEN           -- if MergeUpdate
  UPDATE SET
    [CompanyName] = Source.[CompanyName],
    [Phone] = Source.[Phone]

WHEN NOT MATCHED THEN
  INSERT ([CompanyName], [Phone], [ShipperID])
  VALUES (Source.[CompanyName], Source.[Phone], Source.[ShipperID])

WHEN NOT MATCHED BY SOURCE THEN                       -- if MergeDelete
  DELETE
;

SET IDENTITY_INSERT [dbo].[Shippers] OFF;             -- if identity column exists
ALTER TABLE [dbo].[Shippers] ENABLE TRIGGER ALL;      -- if DisableTriggers

The comment markers (-- if DisableTriggers, -- if identity column exists, -- if MergeUpdate, -- if MergeDelete) indicate sections that are conditionally emitted. With all flags on and an identity column on the table, every block lands in the file; flip any of the flags and the relevant block disappears.

Identity columns

Identity columns are detected automatically. The script wraps the MERGE with SET IDENTITY_INSERT ... ON / OFF, includes the identity in the INSERT clause so explicit values carry, and excludes it from the UPDATE SET clause so existing identity values are never modified.

Computed columns

Computed columns are auto-excluded from extraction and from every section of the generated script. They can't be assigned, so attempting to insert or update them would fail at run time.

DataTongs can do more than write scripts — it can wire the extracted data straight into your schema package's declarative delivery pipeline. Pass --ConfigureDataDelivery and DataTongs writes a DataDelivery block into each matching table's JSON file after extracting the data.

DataTongs --ConfigureDataDelivery
DataTongs --ConfigureDataDelivery --TemplatePath:./Templates/Main

The switch can also be enabled via configuration:

{
  "ShouldCast": { "ConfigureDataDelivery": true }
}

What it does

For every table that produces output, DataTongs opens Tables/<schema>.<table>.json inside the template and writes (or overwrites) a DataDelivery block with:

Tables whose JSON file doesn't exist in the template are skipped with a warning. No file is created from scratch — this tool configures existing tables, it doesn't scaffold new ones (use SchemaTongs for that).

Default MergeType derivation

When a table's config doesn't set MergeType explicitly, the default is derived from the global ShouldCast flags:

A per-table MergeType on the Tables[] entry always wins over the derived default.

Template root discovery

--ConfigureDataDelivery needs to know where your template lives. It uses this precedence:

1. --TemplatePath:<path> CLI switch.
2. TemplatePath config key.
3. Auto-discovery: walk upward from ContentPath looking for the nearest directory containing a Template.json.

If nothing is found, DataTongs logs a warning and skips the configuration step — extraction still completes normally. See Schema Packages — DataDelivery for the full property reference and the schema validation that catches misconfigurations at authoring time.