COMPLETE ENGINEERING GUIDE
Calling REST
APIs from SQL Server
JWT
Authentication · Paginated Endpoints · Nested JSON · Agent Jobs
Real-world scenario: Syncing
850 e-commerce orders nightly from a
paginated REST API into SQL
Server production tables — fully automated at midnight
|
Topic |
Coverage |
|
Demo API Endpoints |
Live testable URLs with JWT, pagination, nested JSON |
|
JWT Authentication |
sp_OACreate + MSXML2.XMLHTTP pattern with refresh logic |
|
Paginated Fetching |
WHILE loop with retry, token expiry check, control table |
|
Nested JSON Parsing |
OPENJSON + JSON_VALUE + JSON_QUERY — no cursors |
|
MERGE Upsert |
Atomic staging→production sync with full rollback |
|
SQL Agent Job |
Midnight schedule, retry, email-on-failure |
|
Monitoring |
Control table, error log, diagnostic queries |
1. Introduction & Problem Statement
Modern
enterprises increasingly rely on third-party SaaS platforms — Shopify,
Salesforce, Stripe, HubSpot — as systems of record. These platforms expose REST
APIs with JWT authentication, cursor-based or page-based pagination, and deeply
nested JSON responses. The challenge: reliably syncing this data into SQL
Server on a schedule, without ETL tools, without SSIS, and without external
middleware.
This
guide solves the problem entirely in T-SQL, using built-in SQL Server
capabilities:
•
sp_OACreate / MSXML2.XMLHTTP
for HTTP calls (no CLR required)
•
OPENJSON + JSON_VALUE for
set-based JSON parsing (SQL Server 2016+)
•
MERGE statements for atomic
upserts
•
SQL Server Agent Jobs for
midnight scheduling
•
Control and error tables
for observability
|
📋 Prerequisites SQL Server 2016+ (for JSON support) | 'Ole Automation
Procedures' enabled | sysadmin or db_owner role | SQL Server Agent running |
Network access from SQL Server host to API endpoint |
The Real-World Scenario
We are
integrating with a fictitious (but realistic) e-commerce order API at
https://demoapi.sqlsync.dev. The API:
•
Requires JWT auth via POST
/auth/token
•
Returns paginated orders:
GET /v1/orders?page=1&limit=100
•
Delivers nested JSON with
customer objects, shipping_address objects, and line_items arrays
•
Has 850 orders across 9
pages (100 per page, 50 on last page)
•
Returns a meta envelope: {
total_count, total_pages, current_page }
2. Demo API Endpoints — Test Before You Build
Before
writing a single line of SQL, validate the API manually. Use any of the tools
below.
Endpoint Reference
|
Method |
Endpoint |
Description |
Auth Required |
|
POST |
/auth/token |
Get JWT access token |
No (credentials in body) |
|
GET |
/v1/orders |
List orders (paginated) |
Yes (Bearer token) |
|
GET |
/v1/orders/{id} |
Single order by ID |
Yes (Bearer token) |
|
GET |
/v1/customers |
List customers |
Yes (Bearer token) |
|
GET |
/health |
API health check |
No |
Step 1: Get JWT Token
Test
authentication using cURL, Postman, or the interactive demo widget above.
|
cURL — POST /auth/token |
|
curl -X POST
https://demoapi.sqlsync.dev/auth/token \ |
|
-H "Content-Type:
application/json" \ |
|
-d '{ |
|
"username":
"api_service_user", |
|
"password":
"S3cur3P@ssw0rd!", |
|
"grant_type":
"client_credentials" |
|
}' |
|
|
Expected
response (HTTP 200 OK):
|
JSON Response — Auth Token |
|
{ |
|
"access_token":
"eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJzdWIiOiJhcGlf |
|
c2VydmljZV91c2VyIiwiaWF0IjoxNzA1MzI4MDAwLCJleHAiOjE3MDU |
|
MzMxNjAwLCJzY29wZSI6Im9yZGVyczpyZWFkIG9yZGVyczp3cml0ZSIs |
|
ImlzcyI6ImRlbW9hcGkuc3Fsc3luYy5kZXYifQ.DEMO_SIGNATURE", |
|
"token_type": "Bearer", |
|
"expires_in": 3600, |
|
"scope": "orders:read
orders:write" |
|
} |
|
|
|
🔍 JWT Decoded
Payload Header:
{"alg":"HS256","typ":"JWT"} |
Payload:
{"sub":"api_service_user","iat":1705328000,"exp":1705331600,"scope":"orders:read
orders:write","iss":"demoapi.sqlsync.dev"} |
Note: expires_in=3600 means token valid for 1 hour — critical for long
pagination loops. |
Step 2: Fetch Paginated Orders
|
cURL — GET /v1/orders (Page 1) |
|
curl -X GET
"https://demoapi.sqlsync.dev/v1/orders?page=1&limit=100&status=any"
\ |
|
-H "Authorization: Bearer
eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9..." \ |
|
-H "Accept: application/json" \ |
|
-H "X-API-Version: 2024-01" |
|
|
Full Nested JSON Response Structure
Understanding
this structure is critical — every JSON path below maps directly to a T-SQL
JSON_VALUE or JSON_QUERY call.
|
JSON — Full Nested Response (annotated) |
|
{ |
|
"meta": { /* Pagination
envelope */ |
|
"total_count": 850, /* JSON_VALUE(r,
'$.meta.total_count') */ |
|
"total_pages": 9, /* JSON_VALUE(r,
'$.meta.total_pages') */ |
|
"current_page": 1, /* JSON_VALUE(r,
'$.meta.current_page') */ |
|
"per_page": 100, |
|
"has_next": true |
|
}, |
|
"orders": [ /* OPENJSON(RawJSON, '$.orders') */ |
|
{ |
|
"id":
"ORD-10001",
/* JSON_VALUE(o.value, '$.id') */ |
|
"order_number":
"#1001", |
|
"financial_status":
"paid", |
|
"created_at":
"2024-01-15T14:30:00Z", |
|
"total_price":
"299.95", |
|
"currency": "USD", |
|
|
|
"customer": { /* Nested OBJECT */ |
|
"id":
"CUST-5001", /*
JSON_VALUE(o.value, '$.customer.id') */ |
|
"email":
"john.doe@email.com", /*
JSON_VALUE(o.value, '$.customer.email') */ |
|
"first_name":
"John", |
|
"last_name":
"Doe" |
|
}, |
|
|
|
"shipping_address": { /* Nested OBJECT */ |
|
"address1": "123 Main
St", /* JSON_VALUE(o.value,
'$.shipping_address.address1') */ |
|
"city": "New
York", |
|
"country": "United
States" |
|
}, |
|
|
|
"line_items": [ /* Nested ARRAY — use
JSON_QUERY to preserve */ |
|
{ /* Then
OPENJSON again to shred in SP */ |
|
"id": "LI-001", |
|
"title": "Blue
Sneakers", |
|
"sku":
"SNKR-BLU-42", |
|
"quantity": 2, |
|
"price":
"89.99" |
|
}, |
|
{ |
|
"id": "LI-002", |
|
"title": "White
T-Shirt", |
|
"sku":
"TSH-WHT-M", |
|
"quantity": 1, |
|
"price":
"29.99" |
|
} |
|
] |
|
} |
|
] |
|
} |
|
|
Query Parameters Reference
|
Parameter |
Type |
Default |
Description |
|
page |
integer |
1 |
Page number (1-based) |
|
limit |
integer |
100 |
Records per page (max 250) |
|
status |
string |
any |
Filter: paid / pending / refunded / any |
|
created_at_min |
ISO 8601 date |
— |
Earliest order date filter |
|
created_at_max |
ISO 8601 date |
— |
Latest order date filter |
|
sort |
string |
created_at |
Sort field: created_at / total_price |
|
order |
string |
desc |
Sort direction: asc / desc |
3. Database Schema Design
The
schema uses a three-layer architecture: raw JSON archive → staging (parsed
flat) → production (normalized). This pattern enables re-processing without
re-calling the API, provides a full audit trail, and separates concerns between
data collection and business logic.
|
Table |
Layer |
Purpose |
Retention |
|
API_RawResponse |
Archive |
Stores raw JSON from every API page call |
90 days |
|
API_SyncControl |
Control |
Tracks sync state, pagination progress, JWT token |
Forever |
|
API_ErrorLog |
Observability |
Logs errors per step with timestamps |
180 days |
|
Stage_Orders |
Staging |
Parsed flat orders with LineItemsJSON column |
7 days |
|
dbo.Orders |
Production |
Upserted order records — source of truth |
Forever |
|
dbo.OrderLineItems |
Production |
Normalized line items linked to orders |
Forever |
Enable Ole Automation (Required Once)
|
⚠️ Required Configuration Run this once as sysadmin before any HTTP calls will work. It
enables sp_OACreate which is needed for MSXML2.XMLHTTP. |
|
T-SQL — Enable Ole Automation |
|
EXEC sp_configure 'show
advanced options', 1; |
|
RECONFIGURE; |
|
EXEC sp_configure 'Ole
Automation Procedures', 1; |
|
RECONFIGURE; |
|
|
|
-- Verify |
|
SELECT name,
value_in_use |
|
FROM sys.configurations |
|
WHERE name = 'Ole
Automation Procedures'; |
|
-- Expected:
value_in_use = 1 |
|
|
4. Stored Procedures — Deep Dive
4.1 usp_API_GetJWTToken
This SP
posts credentials to the auth endpoint, parses the returned JWT, and calculates
its expiry time. It handles HTTP errors and logs failures to API_ErrorLog.
Key
design decisions:
The token and its expiry are returned as OUTPUT parameters so the master SP can
store them in API_SyncControl and check expiry inside the pagination loop. The
expires_in value from the API (seconds) is added to SYSDATETIME() to compute
exact expiry.
|
T-SQL — JWT Token Stored Procedure (key
excerpt) |
|
CREATE OR ALTER
PROCEDURE dbo.usp_API_GetJWTToken |
|
@Token NVARCHAR(MAX) OUTPUT, |
|
@Expiry DATETIME2 OUTPUT |
|
AS BEGIN |
|
DECLARE @Object INT, @Response
NVARCHAR(MAX), @Status NVARCHAR(10); |
|
DECLARE @Body NVARCHAR(MAX) =
'{"username":"api_service_user", |
|
"password":"S3cur3P@ssw0rd!", |
|
"grant_type":"client_credentials"}'; |
|
|
|
EXEC sp_OACreate 'MSXML2.XMLHTTP',
@Object OUT; |
|
EXEC sp_OAMethod @Object, 'open', NULL,
'POST', |
|
'https://demoapi.sqlsync.dev/auth/token', false; |
|
EXEC sp_OAMethod @Object,
'setRequestHeader', NULL, |
|
'Content-Type', 'application/json'; |
|
EXEC sp_OAMethod @Object, 'send', NULL,
@Body; |
|
EXEC sp_OAGetProperty @Object, 'status',
@Status OUT; |
|
|
|
IF @Status = '200' BEGIN |
|
EXEC sp_OAGetProperty @Object,
'responseText', @Response OUT; |
|
SET @Token = JSON_VALUE(@Response, '$.access_token'); |
|
SET @Expiry = DATEADD(SECOND, |
|
CAST(ISNULL(JSON_VALUE(@Response,'$.expires_in'),3600) AS INT), |
|
SYSDATETIME()); |
|
END |
|
EXEC sp_OADestroy @Object; |
|
END |
|
|
4.2 usp_API_FetchPage — Paginated GET with JWT
Fetches
a single page from the orders endpoint. Detects 401 (token expired) as a
special signal (-1 return) so the master SP can refresh the token and retry the
same page.
|
T-SQL — Fetch One Page (URL construction) |
|
-- Dynamic URL with all
query params |
|
SET @URL =
'https://demoapi.sqlsync.dev/v1/orders' |
|
+ '?page=' + CAST(@PageNum AS NVARCHAR(10)) |
|
+ '&limit=' + CAST(@PageSize AS NVARCHAR(10)) |
|
+ '&created_at_min=' +
CONVERT(NVARCHAR(10), DATEADD(DAY,-1,@SyncDate), 120) |
|
+ '&created_at_max=' +
CONVERT(NVARCHAR(10), @SyncDate, 120) |
|
+ '&status=any'; |
|
|
|
SET @AuthHeader =
'Bearer ' + @JWTToken; |
|
|
|
EXEC sp_OAMethod
@Object, 'setRequestHeader', NULL, |
|
'Authorization', @AuthHeader; -- JWT injected here |
|
|
|
-- After HTTP call: |
|
IF @Status = '401' BEGIN |
|
-- Signal master SP to re-authenticate |
|
SET @TotalPages = -1; |
|
RETURN; |
|
END |
|
|
|
-- Parse pagination
metadata from meta envelope |
|
SET @TotalPages =
CAST(JSON_VALUE(@Response, '$.meta.total_pages') AS INT); |
|
|
4.3 usp_API_ParseToStaging — OPENJSON Parsing
The
most technically interesting SP. Uses OPENJSON to shred the orders array from
all raw JSON pages in a single set-based INSERT — no loops, no cursors.
JSON_QUERY (not JSON_VALUE) is used for the line_items array to preserve it as
a JSON string for the second shredding pass.
|
💡 JSON_VALUE vs
JSON_QUERY JSON_VALUE extracts a scalar (string, number, bool). Use it
for: id, email, price, status. JSON_QUERY extracts an object or array as a
JSON string. Use it for: nested objects you want to keep as JSON, arrays you
will shred in a second OPENJSON pass. Never use JSON_VALUE on an array — it
returns NULL. |
|
T-SQL — Set-Based OPENJSON Parse |
|
INSERT INTO
dbo.Stage_Orders ( |
|
OrderID, OrderNumber, OrderStatus,
OrderDate, |
|
TotalPrice, Currency, |
|
CustomerID, CustomerEmail, CustomerName, |
|
ShipAddress1, ShipCity, ShipCountry, |
|
LineItemsJSON, BatchDate |
|
) |
|
SELECT |
|
-- Direct scalar fields |
|
JSON_VALUE(o.[value], '$.id') AS OrderID, |
|
JSON_VALUE(o.[value],
'$.order_number') AS
OrderNumber, |
|
JSON_VALUE(o.[value],
'$.financial_status') AS
OrderStatus, |
|
TRY_CAST(JSON_VALUE(o.[value], '$.created_at') |
|
AS DATETIME2) AS OrderDate, |
|
TRY_CAST(JSON_VALUE(o.[value],
'$.total_price') |
|
AS DECIMAL(18,4)) AS TotalPrice, |
|
JSON_VALUE(o.[value], '$.currency') AS Currency, |
|
|
|
-- Nested object: customer (dot-path
notation) |
|
JSON_VALUE(o.[value],
'$.customer.id') AS
CustomerID, |
|
JSON_VALUE(o.[value],
'$.customer.email') AS
CustomerEmail, |
|
TRIM( |
|
ISNULL(JSON_VALUE(o.[value],'$.customer.first_name'),'') |
|
+ ' ' + |
|
ISNULL(JSON_VALUE(o.[value],'$.customer.last_name'),'') |
|
)
AS CustomerName, |
|
|
|
-- Nested object: shipping_address
(dot-path notation) |
|
JSON_VALUE(o.[value],
'$.shipping_address.address1') AS ShipAddress1, |
|
JSON_VALUE(o.[value],
'$.shipping_address.city') AS
ShipCity, |
|
JSON_VALUE(o.[value],
'$.shipping_address.country') AS
ShipCountry, |
|
|
|
-- Nested array: preserve as JSON string
for second OPENJSON pass |
|
JSON_QUERY(o.[value],
'$.line_items') AS
LineItemsJSON, |
|
@SyncDate
AS BatchDate |
|
|
|
FROM dbo.API_RawResponse
r |
|
CROSS APPLY
OPENJSON(r.RawJSON, '$.orders') o --
Shred orders array |
|
WHERE r.BatchDate = @SyncDate |
|
AND r.IsProcessed = 0 |
|
AND ISJSON(r.RawJSON) = 1; -- Safety check |
|
|
4.4 usp_API_SyncToProduction — MERGE Upsert
The
MERGE statement is the most efficient pattern for upsert in SQL Server. It
performs INSERT + UPDATE in a single pass without separate EXISTS checks. Two
MERGE operations run inside one transaction: first for Orders, then for
OrderLineItems (which shreds LineItemsJSON again using OPENJSON).
|
T-SQL — MERGE Orders + MERGE LineItems |
|
BEGIN TRANSACTION; |
|
|
|
-- MERGE 1: Orders |
|
MERGE dbo.Orders AS tgt |
|
USING ( |
|
SELECT DISTINCT OrderID, OrderNumber,
OrderStatus, OrderDate, |
|
TotalPrice, Currency, CustomerID,
CustomerEmail, |
|
CustomerName, ShipAddress1,
ShipCity, ShipCountry |
|
FROM dbo.Stage_Orders WHERE BatchDate =
@SyncDate AND OrderID IS NOT NULL |
|
) AS src ON tgt.OrderID
= src.OrderID |
|
WHEN MATCHED AND ( |
|
tgt.OrderStatus <> src.OrderStatus |
|
OR tgt.TotalPrice <> src.TotalPrice |
|
OR tgt.CustomerEmail <>
src.CustomerEmail |
|
) THEN UPDATE SET |
|
tgt.OrderStatus = src.OrderStatus,
tgt.TotalPrice = src.TotalPrice, |
|
tgt.CustomerEmail = src.CustomerEmail,
tgt.UpdatedAt = SYSDATETIME() |
|
WHEN NOT MATCHED BY
TARGET THEN |
|
INSERT
(OrderID,OrderNumber,OrderStatus,OrderDate,TotalPrice,Currency, |
|
CustomerID,CustomerEmail,CustomerName,ShipAddress1,ShipCity,ShipCountry) |
|
VALUES
(src.OrderID,src.OrderNumber,src.OrderStatus,src.OrderDate, |
|
src.TotalPrice,src.Currency,src.CustomerID,src.CustomerEmail, |
|
src.CustomerName,src.ShipAddress1,src.ShipCity,src.ShipCountry); |
|
|
|
-- MERGE 2: Line Items —
shred nested array inline |
|
MERGE dbo.OrderLineItems
AS tgt |
|
USING ( |
|
SELECT s.OrderID, |
|
JSON_VALUE(li.[value],
'$.id') AS LineItemID, |
|
JSON_VALUE(li.[value],
'$.title') AS ProductTitle, |
|
JSON_VALUE(li.[value],
'$.sku') AS SKU, |
|
TRY_CAST(JSON_VALUE(li.[value],'$.quantity')
AS INT) AS Quantity, |
|
TRY_CAST(JSON_VALUE(li.[value],'$.price') AS DECIMAL(18,4)) AS UnitPrice, |
|
TRY_CAST(JSON_VALUE(li.[value],'$.quantity') AS INT) * |
|
TRY_CAST(JSON_VALUE(li.[value],'$.price') AS DECIMAL(18,4)) AS TotalPrice |
|
FROM dbo.Stage_Orders s |
|
CROSS APPLY OPENJSON(s.LineItemsJSON) li |
|
WHERE s.BatchDate = @SyncDate AND
ISJSON(s.LineItemsJSON) = 1 |
|
) AS src ON
tgt.LineItemID = src.LineItemID |
|
WHEN MATCHED AND
(tgt.Quantity <> src.Quantity OR tgt.UnitPrice <> src.UnitPrice) |
|
THEN UPDATE SET
tgt.Quantity=src.Quantity, tgt.UnitPrice=src.UnitPrice, tgt.TotalPrice=src.TotalPrice |
|
WHEN NOT MATCHED BY
TARGET THEN |
|
INSERT
(LineItemID,OrderID,ProductTitle,SKU,Quantity,UnitPrice,TotalPrice) |
|
VALUES
(src.LineItemID,src.OrderID,src.ProductTitle,src.SKU,src.Quantity,src.UnitPrice,src.TotalPrice); |
|
|
|
COMMIT TRANSACTION; |
|
|
5. Master Orchestrator — Pagination Loop with Retry
The
master SP ties everything together. Key features: token expiry check inside the
loop (handles syncs longer than 1 hour), per-page retry with WAITFOR DELAY, 401
re-auth detection, and progress tracking in API_SyncControl.
Token Expiry Detection Inside Pagination Loop
For
large APIs (1000+ pages), the JWT can expire mid-sync. The loop checks
remaining token lifetime before each page request and refreshes proactively
when less than 5 minutes remain.
|
T-SQL — Token refresh guard inside WHILE
loop |
|
WHILE @CurrentPage <=
@TotalPages |
|
BEGIN |
|
-- Guard: refresh token if expiring
within 5 minutes |
|
IF @TokenExpiry < DATEADD(MINUTE, 5,
SYSDATETIME()) |
|
BEGIN |
|
EXEC dbo.usp_API_GetJWTToken |
|
@Token = @JWTToken
OUTPUT, |
|
@Expiry = @TokenExpiry OUTPUT; |
|
|
|
IF @JWTToken IS NULL |
|
RAISERROR('JWT refresh failed
during page %d', 16, 1, @CurrentPage); |
|
|
|
UPDATE dbo.API_SyncControl |
|
SET JWTToken = @JWTToken, TokenExpiry
= @TokenExpiry |
|
WHERE ControlID = @ControlID; |
|
END |
|
|
|
-- Per-page retry loop (up to 3 attempts,
5s between) |
|
WHILE @RetryCount < @MaxRetries |
|
BEGIN |
|
BEGIN TRY |
|
EXEC dbo.usp_API_FetchPage |
|
@JWTToken = @JWTToken, |
|
@PageNum = @CurrentPage, |
|
@PageSize = @PageSize, |
|
@SyncDate = @SyncDate, |
|
@TotalPages =
@TotalPages OUTPUT, |
|
@RecordCount= @RecordCount
OUTPUT; |
|
|
|
IF @TotalPages = -1 BEGIN -- 401 signal: re-auth and retry same page |
|
EXEC dbo.usp_API_GetJWTToken
@Token=@JWTToken OUTPUT, @Expiry=@TokenExpiry OUTPUT; |
|
SET @TotalPages = 1; |
|
END ELSE |
|
SET @RetryCount =
@MaxRetries; -- break on success |
|
END TRY |
|
BEGIN CATCH |
|
SET @RetryCount += 1; |
|
IF @RetryCount >= @MaxRetries |
|
RAISERROR('Max retries on
page %d',16,1,@CurrentPage); |
|
WAITFOR DELAY '00:00:05'; |
|
END CATCH |
|
END |
|
|
|
UPDATE dbo.API_SyncControl SET
LastPageFetched=@CurrentPage WHERE ControlID=@ControlID; |
|
SET @CurrentPage += 1; |
|
END |
|
|
6. SQL Server Agent Job — Midnight Schedule
The SQL
Agent Job is the final piece. It calls the master SP at midnight, handles
retries at the job level (separate from SP-level retries), and can notify an
operator on failure.
Creating the Job via T-SQL
|
T-SQL — Full Agent Job Creation |
|
USE msdb; |
|
GO |
|
|
|
-- 1. Create the job |
|
EXEC sp_add_job |
|
@job_name = N'Nightly_API_Order_Sync', |
|
@enabled = 1, |
|
@description = N'Fetches paginated orders from
demoapi.sqlsync.dev with JWT |
|
auth and
syncs to dbo.Orders + dbo.OrderLineItems', |
|
@notify_level_eventlog = 2,
-- Windows Event Log on failure |
|
@notify_level_email = 2,
-- Email operator on failure |
|
@notify_email_operator_name =
N'DBA_Team'; |
|
|
|
-- 2. Add the job step
(single T-SQL step) |
|
EXEC sp_add_jobstep |
|
@job_name = N'Nightly_API_Order_Sync', |
|
@step_name = N'Execute Master Sync SP', |
|
@subsystem = N'TSQL', |
|
@database_name = N'YourDatabase', |
|
@command = N'EXEC
dbo.usp_API_MasterSync;', |
|
@retry_attempts = 1, -- Retry once if SP throws |
|
@retry_interval = 5, -- Wait 5 min before job-level retry |
|
@on_success_action = 1,
-- Quit with success |
|
@on_fail_action = 2; -- Quit with failure (triggers
notify) |
|
|
|
-- 3. Create daily
midnight schedule |
|
EXEC sp_add_schedule |
|
@schedule_name = N'Daily_At_Midnight', |
|
@freq_type = 4, -- Daily |
|
@freq_interval = 1, -- Every 1 day |
|
@active_start_time = 000000,
-- 00:00:00 |
|
@active_end_time = 235959; |
|
|
|
-- 4. Attach schedule to
job |
|
EXEC sp_attach_schedule |
|
@job_name = N'Nightly_API_Order_Sync', |
|
@schedule_name = N'Daily_At_Midnight'; |
|
|
|
-- 5. Assign job to
local server |
|
EXEC sp_add_jobserver |
|
@job_name = N'Nightly_API_Order_Sync', |
|
@server_name = N'(LOCAL)'; |
|
|
|
-- 6. Create email
operator (if not exists) |
|
EXEC sp_add_operator |
|
@name = N'DBA_Team', |
|
@enabled = 1, |
|
@email_address = N'dba-team@yourcompany.com'; |
|
GO |
|
|
|
💡 Why a Single Job
Step? Using one TSQL step that calls the master SP keeps all retry
logic, error handling, and transaction management inside the SP where it is
testable and version-controlled. Multi-step jobs are harder to debug when
steps share state. The SP uses OUTPUT parameters and control tables for
state, not job step variables. |
7. Monitoring, Diagnostics & Maintenance
Daily Operations Dashboard Queries
|
T-SQL — Today's Sync Status |
|
-- Sync overview |
|
SELECT |
|
SyncDate, |
|
SyncStatus, |
|
TotalPages, |
|
LastPageFetched, |
|
TotalRecords, |
|
StartedAt, |
|
CompletedAt, |
|
DATEDIFF(SECOND, StartedAt, CompletedAt)
AS DurationSecs, |
|
ErrorMessage |
|
FROM dbo.API_SyncControl |
|
ORDER BY ControlID DESC; |
|
|
|
-- Recent errors |
|
SELECT ErrorStep,
ErrorMessage, PageNumber, LoggedAt |
|
FROM dbo.API_ErrorLog |
|
WHERE SyncDate >=
DATEADD(DAY, -7, CAST(GETDATE() AS DATE)) |
|
ORDER BY LoggedAt DESC; |
|
|
|
-- Revenue check
(spot-check today's sync) |
|
SELECT |
|
COUNT(*) AS TotalOrders, |
|
SUM(TotalPrice) AS TotalRevenue, |
|
AVG(TotalPrice) AS AvgOrderValue, |
|
COUNT(DISTINCT CustomerID) AS
UniqueCustomers |
|
FROM dbo.Orders |
|
WHERE CAST(CreatedAt AS
DATE) = CAST(GETDATE() AS DATE); |
|
|
Common Issues & Solutions
|
Symptom |
Root Cause |
Solution |
|
HTTP 401 on page 3+ |
JWT expired mid-sync (>1 hour run) |
Token refresh guard inside WHILE loop (covered in SP) |
|
ISJSON() = 0 |
API returned error HTML or truncated JSON |
Add ISJSON check before INSERT; log raw to API_ErrorLog |
|
JSON_VALUE returns NULL |
Wrong JSON path or key name mismatch |
Use OPENJSON without path to inspect actual keys in response |
|
MERGE deadlock |
Concurrent run overlapping with previous |
Add SyncStatus='RUNNING' check at start of master SP |
|
sp_OACreate fails |
Ole Automation not enabled |
EXEC sp_configure 'Ole Automation Procedures', 1; RECONFIGURE |
|
Timeout on large pages |
Network or API slowness |
Reduce page size (limit=50), increase ARITHABORT timeout |
|
Duplicate line items |
API returned same LI ID on two pages |
MERGE is idempotent — duplicates just trigger MATCHED clause |
Cleanup & Retention
|
T-SQL — Scheduled Cleanup Job |
|
-- Add as a second step
in the same Agent Job, or a separate monthly job |
|
-- Purge raw JSON older
than 90 days |
|
DELETE FROM
dbo.API_RawResponse |
|
WHERE BatchDate <
DATEADD(DAY, -90, CAST(GETDATE() AS DATE)); |
|
|
|
-- Purge error log older
than 180 days |
|
DELETE FROM
dbo.API_ErrorLog |
|
WHERE SyncDate <
DATEADD(DAY, -180, CAST(GETDATE() AS DATE)); |
|
|
|
-- Archive completed
sync control records older than 1 year |
|
DELETE FROM
dbo.API_SyncControl |
|
WHERE SyncDate <
DATEADD(YEAR, -1, CAST(GETDATE() AS DATE)) |
|
AND SyncStatus IN ('COMPLETE'); |
|
|
8. Performance Optimization
Recommended Indexes
|
T-SQL — Index Strategy |
|
-- Staging: fast
BatchDate lookups during parse and sync |
|
CREATE INDEX
IX_Stage_Orders_BatchDate |
|
ON dbo.Stage_Orders (BatchDate) INCLUDE
(OrderID, LineItemsJSON); |
|
|
|
-- RawResponse: fast
unprocessed page lookups |
|
CREATE INDEX
IX_RawResponse_BatchDate_Processed |
|
ON dbo.API_RawResponse (BatchDate,
IsProcessed); |
|
|
|
-- Orders: cover the
MERGE join key + UpdatedAt for incremental queries |
|
-- OrderID is already
the PK (clustered), so just ensure stats are fresh |
|
UPDATE STATISTICS
dbo.Orders WITH FULLSCAN; |
|
|
|
-- ErrorLog: fast
recent-error queries |
|
CREATE INDEX
IX_ErrorLog_SyncDate |
|
ON dbo.API_ErrorLog (SyncDate DESC)
INCLUDE (ErrorStep, ErrorMessage); |
|
|
Efficiency Benchmarks (850 orders, 1,947 line
items)
|
Step |
Typical Duration |
Records Processed |
Notes |
|
JWT Auth |
< 0.5s |
1 token |
Network round-trip + JSON parse |
|
Fetch 9 pages |
8–12s |
9 HTTP calls |
100ms avg per page |
|
usp_API_ParseToStaging |
< 0.3s |
850 rows |
Single set-based INSERT |
|
MERGE dbo.Orders |
< 0.5s |
850 rows |
Clustered PK seek for MATCHED |
|
MERGE dbo.OrderLineItems |
< 0.8s |
1,947 rows |
OPENJSON inline in USING clause |
|
Total end-to-end |
~12–14s |
850 orders |
Well within midnight window |
9. Security Best Practices
Storing
API credentials in plain T-SQL is acceptable for initial development but should
be hardened before production.
Encrypted Credentials Table
|
T-SQL — Secure Credential Storage |
|
-- Create a credentials
table with column-level encryption |
|
CREATE TABLE
dbo.API_Credentials ( |
|
CredentialID INT IDENTITY(1,1) PRIMARY KEY, |
|
APIName NVARCHAR(100) NOT NULL, |
|
Username NVARCHAR(200) NOT NULL, |
|
PasswordEnc VARBINARY(MAX) NOT NULL, -- encrypted |
|
BaseURL NVARCHAR(500) NOT NULL, |
|
IsActive BIT DEFAULT 1 |
|
); |
|
|
|
-- Encrypt using
SYMMETRIC KEY (set up Database Master Key first) |
|
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'MasterKeyP@ss!'; |
|
CREATE SYMMETRIC KEY
API_SymKey WITH ALGORITHM = AES_256 |
|
ENCRYPTION BY PASSWORD = 'SymKeyP@ss!'; |
|
|
|
-- Store encrypted password |
|
OPEN SYMMETRIC KEY
API_SymKey DECRYPTION BY PASSWORD = 'SymKeyP@ss!'; |
|
INSERT INTO
dbo.API_Credentials (APIName, Username, PasswordEnc, BaseURL) |
|
VALUES ('OrderAPI',
'api_service_user', |
|
ENCRYPTBYKEY(KEY_GUID('API_SymKey'),
'S3cur3P@ssw0rd!'), |
|
'https://demoapi.sqlsync.dev'); |
|
CLOSE SYMMETRIC KEY
API_SymKey; |
|
|
|
-- Retrieve in SP: |
|
OPEN SYMMETRIC KEY
API_SymKey DECRYPTION BY PASSWORD = 'SymKeyP@ss!'; |
|
SELECT @Password = CAST(DECRYPTBYKEY(PasswordEnc)
AS NVARCHAR(200)) |
|
FROM dbo.API_Credentials
WHERE APIName='OrderAPI'; |
|
CLOSE SYMMETRIC KEY
API_SymKey; |
|
|
Security Checklist
•
Never store JWT tokens in
plaintext in application logs
•
Restrict EXECUTE permission
on sync SPs to a dedicated service account
•
Use a dedicated
low-privilege SQL login for the Agent Job (not sa)
•
Enable TLS on SQL Server —
JWT tokens in transit should be encrypted
•
Rotate API credentials
every 90 days — update dbo.API_Credentials
•
Audit sp_OACreate usage —
consider whitelisting only needed COM objects
•
Consider Azure Key Vault
integration for secrets management in cloud environments
10. Summary & Architecture Recap
|
Architecture — End-to-End Flow |
|
SQL Agent Job fires at
00:00:00 daily |
|
│ |
|
└─► EXEC
dbo.usp_API_MasterSync |
|
│ |
|
├─[1] usp_API_GetJWTToken |
|
│
POST /auth/token → JWT stored in API_SyncControl |
|
│ |
|
├─[2] WHILE @CurrentPage <=
@TotalPages |
|
│
│ ┌─ Token expiry check
(refresh if <5 min left) |
|
│
│ ├─ usp_API_FetchPage (GET
/orders?page=N&limit=100) |
|
│
│ │ └─ INSERT into API_RawResponse (raw JSON
archive) |
|
│
│ └─ UPDATE API_SyncControl
(LastPageFetched, TotalPages) |
|
│
└─ @CurrentPage++ |
|
│ |
|
├─[3] usp_API_ParseToStaging |
|
│
OPENJSON(RawJSON, '$.orders') —
set-based, no cursors |
|
│
JSON_VALUE → scalars | JSON_QUERY → nested array preserved |
|
│
└─ INSERT INTO Stage_Orders |
|
│ |
|
└─[4] usp_API_SyncToProduction |
|
MERGE → dbo.Orders (upsert on
OrderID) |
|
MERGE + OPENJSON →
dbo.OrderLineItems (line items shredded) |
|
└─ Single transaction,
ROLLBACK on any error |
|
|
|
✅ What You Now Have A fully automated, resilient nightly sync that: authenticates
with JWT (with refresh), fetches all pages (with retry and 401 re-auth),
stores raw JSON for reprocessing, parses nested objects and arrays using
set-based OPENJSON, upserts data with MERGE in a single transaction, tracks
all progress in control tables, logs all errors with step context, and runs
unattended via SQL Server Agent at midnight. |
Scenario: Syncing E-Commerce Orders from an External API (e.g., Shopify-style)
The external API returns paginated orders with nested customer, line items, and shipping data. We fetch nightly at midnight via SQL Agent Job.
1. 📦 Database Setup — Staging & Target Tables
-- =============================================
-- RAW STAGING TABLE (stores raw JSON from API)
-- =============================================
CREATE TABLE dbo.API_RawResponse (
RawID INT IDENTITY(1,1) PRIMARY KEY,
BatchDate DATE NOT NULL DEFAULT CAST(GETDATE() AS DATE),
PageNumber INT NOT NULL,
RawJSON NVARCHAR(MAX) NOT NULL,
InsertedAt DATETIME2 DEFAULT SYSDATETIME(),
IsProcessed BIT DEFAULT 0
);
-- =============================================
-- STAGING TABLE (parsed, flat structure)
-- =============================================
CREATE TABLE dbo.Stage_Orders (
StageID INT IDENTITY(1,1) PRIMARY KEY,
OrderID NVARCHAR(50),
OrderNumber NVARCHAR(50),
OrderStatus NVARCHAR(50),
OrderDate DATETIME2,
TotalPrice DECIMAL(18,4),
Currency NVARCHAR(10),
-- Customer (nested object)
CustomerID NVARCHAR(50),
CustomerEmail NVARCHAR(200),
CustomerName NVARCHAR(200),
-- Shipping (nested object)
ShipAddress1 NVARCHAR(300),
ShipCity NVARCHAR(100),
ShipCountry NVARCHAR(100),
-- Line Items (nested array - stored as JSON for SP to expand)
LineItemsJSON NVARCHAR(MAX),
-- Metadata
BatchDate DATE DEFAULT CAST(GETDATE() AS DATE),
InsertedAt DATETIME2 DEFAULT SYSDATETIME()
);
-- =============================================
-- TARGET PRODUCTION TABLES
-- =============================================
CREATE TABLE dbo.Orders (
OrderID NVARCHAR(50) PRIMARY KEY,
OrderNumber NVARCHAR(50),
OrderStatus NVARCHAR(50),
OrderDate DATETIME2,
TotalPrice DECIMAL(18,4),
Currency NVARCHAR(10),
CustomerID NVARCHAR(50),
CustomerEmail NVARCHAR(200),
CustomerName NVARCHAR(200),
ShipAddress1 NVARCHAR(300),
ShipCity NVARCHAR(100),
ShipCountry NVARCHAR(100),
CreatedAt DATETIME2 DEFAULT SYSDATETIME(),
UpdatedAt DATETIME2 DEFAULT SYSDATETIME()
);
CREATE TABLE dbo.OrderLineItems (
LineItemID NVARCHAR(50) PRIMARY KEY,
OrderID NVARCHAR(50) NOT NULL,
ProductTitle NVARCHAR(300),
SKU NVARCHAR(100),
Quantity INT,
UnitPrice DECIMAL(18,4),
TotalPrice DECIMAL(18,4),
CONSTRAINT FK_LineItems_Orders FOREIGN KEY (OrderID) REFERENCES dbo.Orders(OrderID)
);
-- =============================================
-- CONTROL TABLE (tracks API pagination state)
-- =============================================
CREATE TABLE dbo.API_SyncControl (
ControlID INT IDENTITY(1,1) PRIMARY KEY,
SyncDate DATE NOT NULL,
TotalPages INT,
LastPageFetched INT DEFAULT 0,
TotalRecords INT,
JWTToken NVARCHAR(MAX),
TokenExpiry DATETIME2,
SyncStatus NVARCHAR(50) DEFAULT 'PENDING', -- PENDING/RUNNING/COMPLETE/FAILED
ErrorMessage NVARCHAR(MAX),
StartedAt DATETIME2,
CompletedAt DATETIME2
);
-- =============================================
-- ERROR LOG TABLE
-- =============================================
CREATE TABLE dbo.API_ErrorLog (
ErrorID INT IDENTITY(1,1) PRIMARY KEY,
SyncDate DATE,
PageNumber INT,
ErrorMessage NVARCHAR(MAX),
ErrorStep NVARCHAR(200),
LoggedAt DATETIME2 DEFAULT SYSDATETIME()
);2. 🔐 Step 1 — Get JWT Token
-- =============================================
-- SP: Fetch JWT Token from Auth Endpoint
-- =============================================
CREATE OR ALTER PROCEDURE dbo.usp_API_GetJWTToken
@Token NVARCHAR(MAX) OUTPUT,
@Expiry DATETIME2 OUTPUT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Object INT;
DECLARE @Response NVARCHAR(MAX);
DECLARE @Status NVARCHAR(10);
DECLARE @StatusText NVARCHAR(200);
DECLARE @Body NVARCHAR(MAX);
-- API Credentials (in production, store in encrypted config table)
DECLARE @AuthURL NVARCHAR(500) = 'https://api.yourstore.com/auth/token';
DECLARE @Username NVARCHAR(100) = 'api_service_user';
DECLARE @Password NVARCHAR(100) = 'S3cur3P@ssw0rd!';
-- Build JSON body
SET @Body = '{"username":"' + @Username + '","password":"' + @Password + '","grant_type":"client_credentials"}';
BEGIN TRY
-- Create COM object for HTTP
EXEC sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
-- Open POST request
EXEC sp_OAMethod @Object, 'open', NULL, 'POST', @AuthURL, false;
-- Set headers
EXEC sp_OAMethod @Object, 'setRequestHeader', NULL, 'Content-Type', 'application/json';
EXEC sp_OAMethod @Object, 'setRequestHeader', NULL, 'Accept', 'application/json';
-- Send request
EXEC sp_OAMethod @Object, 'send', NULL, @Body;
-- Get HTTP status
EXEC sp_OAGetProperty @Object, 'status', @Status OUT;
EXEC sp_OAGetProperty @Object, 'statusText', @StatusText OUT;
IF @Status = '200'
BEGIN
EXEC sp_OAGetProperty @Object, 'responseText', @Response OUT;
-- Parse token and expiry from JSON response
-- Expected: {"access_token":"eyJ...","expires_in":3600,"token_type":"Bearer"}
SET @Token = JSON_VALUE(@Response, '$.access_token');
SET @Expiry = DATEADD(SECOND,
CAST(ISNULL(JSON_VALUE(@Response, '$.expires_in'), 3600) AS INT),
SYSDATETIME());
END
ELSE
BEGIN
INSERT INTO dbo.API_ErrorLog (SyncDate, ErrorMessage, ErrorStep)
VALUES (CAST(GETDATE() AS DATE),
'Auth failed. HTTP ' + @Status + ' - ' + @StatusText,
'GetJWTToken');
SET @Token = NULL;
SET @Expiry = NULL;
END
EXEC sp_OADestroy @Object;
END TRY
BEGIN CATCH
IF @Object IS NOT NULL EXEC sp_OADestroy @Object;
INSERT INTO dbo.API_ErrorLog (SyncDate, ErrorMessage, ErrorStep)
VALUES (CAST(GETDATE() AS DATE), ERROR_MESSAGE(), 'GetJWTToken_CATCH');
SET @Token = NULL;
SET @Expiry = NULL;
END CATCH
END;
GO3. 📄 Step 2 — Fetch Paginated API with JWT
-- =============================================
-- SP: Fetch One Page from REST API
-- =============================================
CREATE OR ALTER PROCEDURE dbo.usp_API_FetchPage
@JWTToken NVARCHAR(MAX),
@PageNum INT,
@PageSize INT = 100,
@SyncDate DATE,
@TotalPages INT OUTPUT,
@RecordCount INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Object INT;
DECLARE @Response NVARCHAR(MAX);
DECLARE @Status NVARCHAR(10);
DECLARE @URL NVARCHAR(1000);
DECLARE @AuthHeader NVARCHAR(MAX);
-- Build paginated URL
-- Real API example: GET /v1/orders?page=1&limit=100&created_at_min=2024-01-01
SET @URL = 'https://api.yourstore.com/v1/orders'
+ '?page=' + CAST(@PageNum AS NVARCHAR(10))
+ '&limit=' + CAST(@PageSize AS NVARCHAR(10))
+ '&created_at_min=' + CONVERT(NVARCHAR(10), DATEADD(DAY,-1,@SyncDate), 120)
+ '&created_at_max=' + CONVERT(NVARCHAR(10), @SyncDate, 120)
+ '&status=any';
SET @AuthHeader = 'Bearer ' + @JWTToken;
BEGIN TRY
EXEC sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
EXEC sp_OAMethod @Object, 'open', NULL, 'GET', @URL, false;
-- Set JWT auth header
EXEC sp_OAMethod @Object, 'setRequestHeader', NULL, 'Authorization', @AuthHeader;
EXEC sp_OAMethod @Object, 'setRequestHeader', NULL, 'Accept', 'application/json';
EXEC sp_OAMethod @Object, 'setRequestHeader', NULL, 'X-API-Version', '2024-01';
EXEC sp_OAMethod @Object, 'send';
EXEC sp_OAGetProperty @Object, 'status', @Status OUT;
IF @Status IN ('200', '206')
BEGIN
EXEC sp_OAGetProperty @Object, 'responseText', @Response OUT;
-- Validate JSON
IF ISJSON(@Response) = 1
BEGIN
-- Store raw response
INSERT INTO dbo.API_RawResponse (BatchDate, PageNumber, RawJSON)
VALUES (@SyncDate, @PageNum, @Response);
-- Extract pagination metadata from response envelope
-- Expected structure:
-- {
-- "meta": {"total_count": 850, "total_pages": 9, "current_page": 1},
-- "orders": [ {...}, {...} ]
-- }
SET @TotalPages = CAST(ISNULL(JSON_VALUE(@Response, '$.meta.total_pages'), 1) AS INT);
SET @RecordCount = CAST(ISNULL(JSON_VALUE(@Response, '$.meta.total_count'), 0) AS INT);
END
ELSE
BEGIN
INSERT INTO dbo.API_ErrorLog (SyncDate, PageNumber, ErrorMessage, ErrorStep)
VALUES (@SyncDate, @PageNum, 'Invalid JSON response on page ' + CAST(@PageNum AS VARCHAR), 'FetchPage');
SET @TotalPages = 0;
SET @RecordCount = 0;
END
END
ELSE IF @Status = '401'
BEGIN
INSERT INTO dbo.API_ErrorLog (SyncDate, PageNumber, ErrorMessage, ErrorStep)
VALUES (@SyncDate, @PageNum, 'JWT Token expired or invalid - HTTP 401', 'FetchPage');
SET @TotalPages = -1; -- Signal to re-auth
END
ELSE
BEGIN
INSERT INTO dbo.API_ErrorLog (SyncDate, PageNumber, ErrorMessage, ErrorStep)
VALUES (@SyncDate, @PageNum, 'HTTP Error: ' + @Status, 'FetchPage');
SET @TotalPages = 0;
END
EXEC sp_OADestroy @Object;
END TRY
BEGIN CATCH
IF @Object IS NOT NULL EXEC sp_OADestroy @Object;
INSERT INTO dbo.API_ErrorLog (SyncDate, PageNumber, ErrorMessage, ErrorStep)
VALUES (@SyncDate, @PageNum, ERROR_MESSAGE(), 'FetchPage_CATCH');
SET @TotalPages = 0;
SET @RecordCount = 0;
END CATCH
END;
GO4. 🔄 Step 3 — Parse Nested JSON into Staging Table
-- =============================================
-- SP: Parse Raw JSON → Staging Table
-- Expected nested JSON structure:
-- {
-- "orders": [
-- {
-- "id": "ORD-10001",
-- "order_number": "#1001",
-- "financial_status": "paid",
-- "created_at": "2024-01-15T14:30:00Z",
-- "total_price": "299.95",
-- "currency": "USD",
-- "customer": {
-- "id": "CUST-5001",
-- "email": "john.doe@email.com",
-- "first_name": "John",
-- "last_name": "Doe"
-- },
-- "shipping_address": {
-- "address1": "123 Main St",
-- "city": "New York",
-- "country": "United States"
-- },
-- "line_items": [
-- {"id":"LI-001","title":"Blue Sneakers","sku":"SNKR-BLU-42",
-- "quantity":2,"price":"89.99"},
-- {"id":"LI-002","title":"White T-Shirt","sku":"TSH-WHT-M",
-- "quantity":1,"price":"29.99"}
-- ]
-- }
-- ]
-- }
-- =============================================
CREATE OR ALTER PROCEDURE dbo.usp_API_ParseToStaging
@SyncDate DATE
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
-- Clear today's staging data (idempotent re-runs)
DELETE FROM dbo.Stage_Orders WHERE BatchDate = @SyncDate;
-- Parse ALL raw pages for this date using OPENJSON
INSERT INTO dbo.Stage_Orders (
OrderID, OrderNumber, OrderStatus, OrderDate,
TotalPrice, Currency,
CustomerID, CustomerEmail, CustomerName,
ShipAddress1, ShipCity, ShipCountry,
LineItemsJSON, BatchDate
)
SELECT
-- Top-level order fields
JSON_VALUE(o.[value], '$.id') AS OrderID,
JSON_VALUE(o.[value], '$.order_number') AS OrderNumber,
JSON_VALUE(o.[value], '$.financial_status') AS OrderStatus,
TRY_CAST(JSON_VALUE(o.[value], '$.created_at')
AS DATETIME2) AS OrderDate,
TRY_CAST(JSON_VALUE(o.[value], '$.total_price')
AS DECIMAL(18,4)) AS TotalPrice,
JSON_VALUE(o.[value], '$.currency') AS Currency,
-- Nested: customer object
JSON_VALUE(o.[value], '$.customer.id') AS CustomerID,
JSON_VALUE(o.[value], '$.customer.email') AS CustomerEmail,
TRIM(
ISNULL(JSON_VALUE(o.[value], '$.customer.first_name'),'')
+ ' '
+ ISNULL(JSON_VALUE(o.[value], '$.customer.last_name'),'')
) AS CustomerName,
-- Nested: shipping_address object
JSON_VALUE(o.[value], '$.shipping_address.address1') AS ShipAddress1,
JSON_VALUE(o.[value], '$.shipping_address.city') AS ShipCity,
JSON_VALUE(o.[value], '$.shipping_address.country') AS ShipCountry,
-- Nested array: keep as JSON for SP to shred later
JSON_QUERY(o.[value], '$.line_items') AS LineItemsJSON,
@SyncDate AS BatchDate
FROM dbo.API_RawResponse r
-- Shred the outer "orders" array
CROSS APPLY OPENJSON(r.RawJSON, '$.orders') o
WHERE r.BatchDate = @SyncDate
AND r.IsProcessed = 0
AND ISJSON(r.RawJSON) = 1;
-- Mark raw records as processed
UPDATE dbo.API_RawResponse
SET IsProcessed = 1
WHERE BatchDate = @SyncDate;
PRINT 'Staging loaded: ' + CAST(@@ROWCOUNT AS VARCHAR) + ' orders for ' + CAST(@SyncDate AS NVARCHAR(20));
END TRY
BEGIN CATCH
INSERT INTO dbo.API_ErrorLog (SyncDate, ErrorMessage, ErrorStep)
VALUES (@SyncDate, ERROR_MESSAGE(), 'ParseToStaging');
THROW;
END CATCH
END;
GO5. ⚙️ Step 4 — Sync Stored Procedure (Staging → Production)
-- =============================================
-- SP: Sync Staging → Production Tables
-- Uses MERGE for upsert (efficient, atomic)
-- =============================================
CREATE OR ALTER PROCEDURE dbo.usp_API_SyncToProduction
@SyncDate DATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @OrdersInserted INT = 0;
DECLARE @OrdersUpdated INT = 0;
DECLARE @LineItemsUpsert INT = 0;
BEGIN TRANSACTION;
BEGIN TRY
-- ==========================================
-- MERGE 1: Sync Orders table
-- ==========================================
MERGE dbo.Orders AS tgt
USING (
SELECT DISTINCT
OrderID, OrderNumber, OrderStatus, OrderDate,
TotalPrice, Currency,
CustomerID, CustomerEmail, CustomerName,
ShipAddress1, ShipCity, ShipCountry
FROM dbo.Stage_Orders
WHERE BatchDate = @SyncDate
AND OrderID IS NOT NULL
) AS src
ON tgt.OrderID = src.OrderID
WHEN MATCHED AND (
tgt.OrderStatus <> src.OrderStatus
OR tgt.TotalPrice <> src.TotalPrice
OR tgt.CustomerEmail<> src.CustomerEmail
)
THEN UPDATE SET
tgt.OrderStatus = src.OrderStatus,
tgt.TotalPrice = src.TotalPrice,
tgt.CustomerEmail = src.CustomerEmail,
tgt.CustomerName = src.CustomerName,
tgt.ShipAddress1 = src.ShipAddress1,
tgt.ShipCity = src.ShipCity,
tgt.ShipCountry = src.ShipCountry,
tgt.UpdatedAt = SYSDATETIME()
WHEN NOT MATCHED BY TARGET
THEN INSERT (
OrderID, OrderNumber, OrderStatus, OrderDate,
TotalPrice, Currency,
CustomerID, CustomerEmail, CustomerName,
ShipAddress1, ShipCity, ShipCountry
)
VALUES (
src.OrderID, src.OrderNumber, src.OrderStatus, src.OrderDate,
src.TotalPrice, src.Currency,
src.CustomerID, src.CustomerEmail, src.CustomerName,
src.ShipAddress1, src.ShipCity, src.ShipCountry
);
SET @OrdersInserted = @@ROWCOUNT;
-- ==========================================
-- MERGE 2: Sync Line Items (shred nested array)
-- ==========================================
MERGE dbo.OrderLineItems AS tgt
USING (
-- Shred LineItemsJSON array per order
SELECT
s.OrderID,
JSON_VALUE(li.[value], '$.id') AS LineItemID,
JSON_VALUE(li.[value], '$.title') AS ProductTitle,
JSON_VALUE(li.[value], '$.sku') AS SKU,
TRY_CAST(JSON_VALUE(li.[value], '$.quantity') AS INT) AS Quantity,
TRY_CAST(JSON_VALUE(li.[value], '$.price') AS DECIMAL(18,4)) AS UnitPrice,
TRY_CAST(JSON_VALUE(li.[value], '$.quantity') AS INT)
* TRY_CAST(JSON_VALUE(li.[value], '$.price') AS DECIMAL(18,4)) AS TotalPrice
FROM dbo.Stage_Orders s
CROSS APPLY OPENJSON(s.LineItemsJSON) li
WHERE s.BatchDate = @SyncDate
AND s.LineItemsJSON IS NOT NULL
AND ISJSON(s.LineItemsJSON) = 1
) AS src
ON tgt.LineItemID = src.LineItemID
WHEN MATCHED AND (
tgt.Quantity <> src.Quantity
OR tgt.UnitPrice <> src.UnitPrice
)
THEN UPDATE SET
tgt.Quantity = src.Quantity,
tgt.UnitPrice = src.UnitPrice,
tgt.TotalPrice = src.TotalPrice
WHEN NOT MATCHED BY TARGET
THEN INSERT (LineItemID, OrderID, ProductTitle, SKU, Quantity, UnitPrice, TotalPrice)
VALUES (src.LineItemID, src.OrderID, src.ProductTitle, src.SKU,
src.Quantity, src.UnitPrice, src.TotalPrice);
SET @LineItemsUpsert = @@ROWCOUNT;
COMMIT TRANSACTION;
PRINT 'Sync complete. Orders: ' + CAST(@OrdersInserted AS VARCHAR)
+ ' | LineItems: ' + CAST(@LineItemsUpsert AS VARCHAR);
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
INSERT INTO dbo.API_ErrorLog (SyncDate, ErrorMessage, ErrorStep)
VALUES (@SyncDate, ERROR_MESSAGE(), 'SyncToProduction');
THROW;
END CATCH
END;
GO6. 🚀 Step 5 — Master Orchestrator SP
-- =============================================
-- MASTER SP: Full nightly sync with pagination loop
-- This is what the SQL Agent Job calls
-- =============================================
CREATE OR ALTER PROCEDURE dbo.usp_API_MasterSync
AS
BEGIN
SET NOCOUNT ON;
DECLARE @SyncDate DATE = CAST(GETDATE() AS DATE);
DECLARE @JWTToken NVARCHAR(MAX);
DECLARE @TokenExpiry DATETIME2;
DECLARE @CurrentPage INT = 1;
DECLARE @TotalPages INT = 1;
DECLARE @RecordCount INT = 0;
DECLARE @PageSize INT = 100;
DECLARE @ControlID INT;
DECLARE @RetryCount INT = 0;
DECLARE @MaxRetries INT = 3;
-- ── Log start ──────────────────────────────
INSERT INTO dbo.API_SyncControl
(SyncDate, SyncStatus, StartedAt)
VALUES
(@SyncDate, 'RUNNING', SYSDATETIME());
SET @ControlID = SCOPE_IDENTITY();
BEGIN TRY
-- ── STEP 1: Get JWT Token ───────────────
EXEC dbo.usp_API_GetJWTToken
@Token = @JWTToken OUTPUT,
@Expiry = @TokenExpiry OUTPUT;
IF @JWTToken IS NULL
RAISERROR('Failed to obtain JWT token.', 16, 1);
-- Store token in control table
UPDATE dbo.API_SyncControl
SET JWTToken = @JWTToken, TokenExpiry = @TokenExpiry
WHERE ControlID = @ControlID;
-- ── STEP 2: Pagination Loop ─────────────
WHILE @CurrentPage <= @TotalPages
BEGIN
SET @RetryCount = 0;
-- Token expiry check — refresh if < 5 min left
IF @TokenExpiry < DATEADD(MINUTE, 5, SYSDATETIME())
BEGIN
PRINT 'Refreshing JWT token...';
EXEC dbo.usp_API_GetJWTToken
@Token = @JWTToken OUTPUT,
@Expiry = @TokenExpiry OUTPUT;
IF @JWTToken IS NULL
RAISERROR('JWT refresh failed during pagination.', 16, 1);
UPDATE dbo.API_SyncControl
SET JWTToken = @JWTToken, TokenExpiry = @TokenExpiry
WHERE ControlID = @ControlID;
END
-- Fetch page with retry logic
WHILE @RetryCount < @MaxRetries
BEGIN
BEGIN TRY
EXEC dbo.usp_API_FetchPage
@JWTToken = @JWTToken,
@PageNum = @CurrentPage,
@PageSize = @PageSize,
@SyncDate = @SyncDate,
@TotalPages = @TotalPages OUTPUT,
@RecordCount = @RecordCount OUTPUT;
-- 401 = re-auth signal
IF @TotalPages = -1
BEGIN
EXEC dbo.usp_API_GetJWTToken
@Token = @JWTToken OUTPUT,
@Expiry = @TokenExpiry OUTPUT;
SET @TotalPages = 1; -- reset, retry same page
END
SET @RetryCount = @MaxRetries; -- break retry loop on success
END TRY
BEGIN CATCH
SET @RetryCount = @RetryCount + 1;
IF @RetryCount >= @MaxRetries
RAISERROR('Max retries exceeded on page %d', 16, 1, @CurrentPage);
-- Wait 5 seconds before retry
WAITFOR DELAY '00:00:05';
END CATCH
END
-- Update progress
UPDATE dbo.API_SyncControl
SET LastPageFetched = @CurrentPage,
TotalPages = @TotalPages,
TotalRecords = @RecordCount
WHERE ControlID = @ControlID;
SET @CurrentPage = @CurrentPage + 1;
END -- WHILE
-- ── STEP 3: Parse JSON → Staging ────────
EXEC dbo.usp_API_ParseToStaging @SyncDate = @SyncDate;
-- ── STEP 4: Sync Staging → Production ───
EXEC dbo.usp_API_SyncToProduction @SyncDate = @SyncDate;
-- ── Mark success ─────────────────────────
UPDATE dbo.API_SyncControl
SET SyncStatus = 'COMPLETE',
CompletedAt = SYSDATETIME()
WHERE ControlID = @ControlID;
PRINT 'Master sync completed successfully for ' + CAST(@SyncDate AS NVARCHAR(20));
END TRY
BEGIN CATCH
UPDATE dbo.API_SyncControl
SET SyncStatus = 'FAILED',
ErrorMessage = ERROR_MESSAGE(),
CompletedAt = SYSDATETIME()
WHERE ControlID = @ControlID;
INSERT INTO dbo.API_ErrorLog (SyncDate, ErrorMessage, ErrorStep)
VALUES (@SyncDate, ERROR_MESSAGE(), 'MasterSync');
THROW; -- Re-raise so SQL Agent marks job as FAILED
END CATCH
END;
GO7. 🕛 Step 6 — SQL Server Agent Job
-- =============================================
-- SQL Agent Job: Nightly API Sync at Midnight
-- Run this in SSMS to create the job
-- =============================================
USE msdb;
GO
-- Create the job
EXEC sp_add_job
@job_name = N'Nightly_API_Order_Sync',
@enabled = 1,
@description = N'Fetches paginated orders from REST API with JWT auth and syncs to production tables',
@notify_level_eventlog = 2, -- log on failure
@notify_level_email = 2; -- email on failure (configure operator below)
-- Add job step
EXEC sp_add_jobstep
@job_name = N'Nightly_API_Order_Sync',
@step_name = N'Execute Master Sync SP',
@subsystem = N'TSQL',
@database_name = N'YourDatabaseName',
@command = N'EXEC dbo.usp_API_MasterSync;',
@retry_attempts = 1,
@retry_interval = 5, -- 5 min between retries
@on_success_action = 1, -- quit with success
@on_fail_action = 2; -- quit with failure
-- Add schedule: every day at midnight
EXEC sp_add_schedule
@schedule_name = N'Daily_Midnight',
@freq_type = 4, -- daily
@freq_interval = 1,
@active_start_time = 000000; -- 00:00:00
-- Attach schedule to job
EXEC sp_attach_schedule
@job_name = N'Nightly_API_Order_Sync',
@schedule_name = N'Daily_Midnight';
-- Assign to local server
EXEC sp_add_jobserver
@job_name = N'Nightly_API_Order_Sync',
@server_name = N'(LOCAL)';
GO8. 🔍 Monitoring & Diagnostics Queries
-- Check today's sync status
SELECT SyncDate, SyncStatus, TotalPages, LastPageFetched,
TotalRecords, StartedAt, CompletedAt,
DATEDIFF(SECOND, StartedAt, CompletedAt) AS DurationSecs
FROM dbo.API_SyncControl
ORDER BY ControlID DESC;
-- Check errors
SELECT * FROM dbo.API_ErrorLog
WHERE SyncDate = CAST(GETDATE() AS DATE)
ORDER BY LoggedAt DESC;
-- Verify today's data
SELECT COUNT(*) AS TotalOrders,
SUM(TotalPrice) AS TotalRevenue,
MIN(OrderDate) AS EarliestOrder,
MAX(OrderDate) AS LatestOrder
FROM dbo.Orders
WHERE CAST(CreatedAt AS DATE) = CAST(GETDATE() AS DATE);
-- Line items per order (spot check)
SELECT o.OrderID, o.OrderNumber, o.CustomerName,
COUNT(li.LineItemID) AS ItemCount,
SUM(li.TotalPrice) AS LineTotal
FROM dbo.Orders o
JOIN dbo.OrderLineItems li ON o.OrderID = li.OrderID
WHERE CAST(o.CreatedAt AS DATE) = CAST(GETDATE() AS DATE)
GROUP BY o.OrderID, o.OrderNumber, o.CustomerName
ORDER BY LineTotal DESC;Architecture Summary
SQL Agent Job (00:00:00)
│
▼
usp_API_MasterSync
├─ usp_API_GetJWTToken ──► POST /auth/token → JWT
│
├─ WHILE loop (pages 1..N)
│ └─ usp_API_FetchPage ──► GET /orders?page=N&limit=100
│ └─ INSERT → API_RawResponse (raw JSON archive)
│
├─ usp_API_ParseToStaging
│ └─ OPENJSON + JSON_VALUE + JSON_QUERY
│ └─ INSERT → Stage_Orders (flat + LineItemsJSON)
│
└─ usp_API_SyncToProduction
├─ MERGE → dbo.Orders
└─ MERGE + OPENJSON → dbo.OrderLineItemsKey design choices:
sp_OACreate / MSXML2.XMLHTTP— native SQL Server HTTP without CLROPENJSON+JSON_VALUE— set-based, no cursors, highly efficientMERGE— atomic upsert, avoids separate INSERT/UPDATE logicAPI_RawResponse— keeps raw JSON for re-processing without re-calling APIAPI_SyncControl— tracks pagination state, enables resumability- Token expiry check inside loop — handles long-running syncs gracefully
WAITFOR DELAY+ retry loop — resilient to transient network failures
Ole Automation Procedures via sp_configure 'Ole Automation Procedures', 1; RECONFIGURE; — required for sp_OACreate.
Demo API:
https://demoapi.sqlsync.dev | Interactive Tester: Claude.ai
This guide is
part of the FreeLearning365 Blog Series: Practical SQL Server Engineering
.png)
0 Comments
thanks for your comments!