Generate SQLBatch dsync YAML configuration for mapping SQL database tables to NoSQL document structures. Use when user needs to create dsync config for SQL Server, DB2, or PostgreSQL to CosmosDB/MongoDB sync with CDC support.
How this skill is triggered — by the user, by Claude, or both
Slash command
/dsync-sqlbatch-mapping:dsync-sqlbatch-mappingThe summary Claude sees in its skill listing — used to decide when to auto-load this skill
Generate a dsync SQLBatch YAML configuration file that maps relational SQL tables to NoSQL document structures with support for initial sync and CDC (Change Data Capture).
Generate a dsync SQLBatch YAML configuration file that maps relational SQL tables to NoSQL document structures with support for initial sync and CDC (Change Data Capture).
Before generating the config, gather:
sqlserver, db2, or postgresdriver: sqlserver # or db2, postgres
connectionstring: sqlserver://user:password@host:port?database=DbName
mappings:
- namespace: ContainerName.documentType # Unique per mapping
countquery: "SELECT COUNT(1) FROM Table"
partitionquery: "SELECT 'prefix_' + CAST(ID AS VARCHAR) AS ID FROM Table TABLESAMPLE (1 PERCENT)"
query: |
SELECT
'prefix_' + CAST(t.ID AS VARCHAR) AS ID,
'prefix_' + CAST(t.ID AS VARCHAR) AS partitionKey,
'doctype' AS type,
t.Column1 AS field1,
(SELECT ... FOR JSON PATH) AS embeddedArray
FROM Table t
limit: 500
cols: ["ID"] # MUST be uppercase
decodejson: ["embeddedArray"] # List embedded JSON fields
changes:
- query: "SELECT ... FROM CHANGETABLE(CHANGES Table, @p1) AS CT ..."
initialcursorquery: "SELECT CHANGE_TRACKING_CURRENT_VERSION()"
ID in query aliases and cols arrayCAST(t.TableID AS VARCHAR) AS ID with cols: ["ID"]Container.doctype formatCustomerOrders.customers, CustomerOrders.ordersmoney type gets base64 encoded - CAST to FLOATCAST(t.Price AS FLOAT) AS priceFOR JSON PATH for arrays, FOR JSON PATH, WITHOUT_ARRAY_WRAPPER for objectsdecodejson arrayCT.SYS_CHANGE_OPERATION directly'U' AS SYS_CHANGE_OPERATION (always treat as update)WHERE ... IS NOT NULL for joins that may not match:
SELECT 'prefix_' + CAST(t.ID AS VARCHAR) AS ID, 'U' AS SYS_CHANGE_OPERATION,
CHANGE_TRACKING_CURRENT_VERSION()
FROM CHANGETABLE(CHANGES RelatedTable, @p1) AS CT
JOIN MainTable t ON t.RelatedID = CT.RelatedID
WHERE t.ID IS NOT NULL -- Prevents null ID errors
-- CORRECT: Join on ProductReviewID
JOIN Production.ProductReview prv ON prv.ProductReviewID = CT.ProductReviewID
-- WRONG: CT doesn't have ProductID
JOIN Production.Product pr ON pr.ProductID = CT.ProductID
driver: sqlserver
connectionstring: sqlserver://sa:[email protected]:1433?database=DbName
FOR JSON PATH, FOR JSON PATH, WITHOUT_ARRAY_WRAPPERCHANGETABLE(CHANGES TableName, @p1) with CHANGE_TRACKING_CURRENT_VERSION()templates/enable-change-tracking.sqldriver: db2
connectionstring: db2://user:password@host:50000?database=DbName
LISTAGG with manual JSON construction (no native JSON_ARRAYAGG)ASNCDC.CDC_SCHEMA_TABLE tables with IBMSNAP_COMMITSEQTest Initial Sync First
docker run --rm --entrypoint /simple \
-v ./config.yaml:/cfg.yml \
markadiom/dsynct-alx --log-level INFO \
sync --skip-change-stream --dst-data-type DATA_TYPE_JSON_ID \
--namespace "Namespace" \
sqlbatch --config=cfg.yml /dev/null
Check JSON Output
Test CDC (requires change tracking enabled)
# Full sync with CDC
docker run --rm --entrypoint /simple \
-v ./config.yaml:/cfg.yml \
markadiom/dsynct-alx --log-level INFO \
sync --dst-data-type DATA_TYPE_JSON_ID \
--namespace "Namespace" \
sqlbatch --config=cfg.yml /dev/null
Test CDC-Only Mode
# Skip initial sync, only capture changes
docker run --rm --entrypoint /simple \
-v ./config.yaml:/cfg.yml \
markadiom/dsynct-alx --log-level INFO \
sync --skip-initial-sync --dst-data-type DATA_TYPE_JSON_ID \
--namespace "Namespace" \
sqlbatch --config=cfg.yml /dev/null
| Error | Cause | Fix |
|---|---|---|
query column missing ID | Lowercase id in query | Use AS ID (uppercase) |
string(null_type) | Null ID from change query | Add WHERE t.ID IS NOT NULL |
| Base64 in numeric fields | SQL money/decimal type | CAST(col AS FLOAT) |
Change tracking not enabled | Table not configured | Run enable-change-tracking.sql |
Invalid column name in CDC | Wrong PK in CHANGETABLE join | Join on table's actual primary key |
connection refused | Docker can't reach localhost | Use host.docker.internal |
no compatible data path | MongoDB requires BSON, SQLBatch outputs JSON_ID | Use transformer pipeline (see MongoDB section) |
| Nested objects as JSON strings | Nested FOR JSON subqueries | Use [parent.child] bracket notation instead |
CRITICAL: MongoDB connector only accepts BSON data type, not JSON_ID. When using SQLBatch with MongoDB destination, you MUST use a transformer.
dsync-transform.yaml):mappings:
- namespace: sourceNamespace
mapnamespace: DatabaseName.collectionName
- namespace: anotherNamespace
mapnamespace: DatabaseName.anotherCollection
--transform flag and transformer destination:docker run --rm \
-e 'DSYNCT_MODE=simple' \
-v ./config.yaml:/cfg.yaml \
-v ./dsync-transform.yaml:/transform.yaml \
markadiom/dsynct \
--log-level INFO \
sync --transform --skip-change-stream \
--namespace "myNamespace" \
sqlbatch --config=/cfg.yaml "mongodb://host:27017" dsync-transform:///transform.yaml
Pipeline flow: SQLBatch (JSON_ID) → Transformer → MongoDB (BSON)
The decodejson array only works for top-level JSON fields. Nested JSON strings inside decoded objects remain as strings.
-- This creates a JSON string for parentCategory, not a nested object
(
SELECT
pc.ProductCategoryID AS productCategoryId,
pc.Name AS name,
(
SELECT ppc.ProductCategoryID AS productCategoryId, ppc.Name AS name
FROM ProductCategory ppc
WHERE ppc.ProductCategoryID = pc.ParentProductCategoryID
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
) AS parentCategory -- This becomes a JSON string!
FROM ProductCategory pc
WHERE pc.ProductCategoryID = p.ProductCategoryID
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
) AS category
-- Use [dotted.path] notation to create proper nested objects
(
SELECT
pc.ProductCategoryID AS productCategoryId,
pc.Name AS name,
ppc.ProductCategoryID AS [parentCategory.productCategoryId],
ppc.Name AS [parentCategory.name]
FROM ProductCategory pc
LEFT JOIN ProductCategory ppc ON ppc.ProductCategoryID = pc.ParentProductCategoryID
WHERE pc.ProductCategoryID = p.ProductCategoryID
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
) AS category
This produces properly nested objects without needing nested decodejson.
Same pattern works for arrays:
-- For lineItems with embedded product
(
SELECT
sod.OrderQty AS orderQty,
sod.UnitPrice AS unitPrice,
p.ProductID AS [product.productId],
p.Name AS [product.name],
p.Color AS [product.color]
FROM SalesOrderDetail sod
JOIN Product p ON p.ProductID = sod.ProductID
WHERE sod.SalesOrderID = soh.SalesOrderID
FOR JSON PATH
) AS lineItems
Generate the complete YAML config file with:
Provides UI/UX resources: 50+ styles, color palettes, font pairings, guidelines, charts for web/mobile across React, Next.js, Vue, Svelte, Tailwind, React Native, Flutter. Aids planning, building, reviewing interfaces.
Searches MemPalace before answering questions about past work, people, projects, or prior decisions. Returns verbatim stored content instead of guessing from model memory.
npx claudepluginhub adiom-data/robo-skills --plugin dsync-sqlbatch-mapping