Reading a JSON Lines File Into a Table With OPENROWSET

The export looks like JSON, but parsing the whole file fails at the second object. JSON Lines stores one JSON object on each physical line rather than one enclosing array. Read the file once, preserve line numbers, and validate each line before converting fields into a table.

Confirm the JSON Lines File Contract

Ask for UTF-8, one object per line, and a documented field schema. A quoted newline inside a JSON string is escaped as characters, so it does not split the physical record. Pretty-printed objects spanning several physical lines do not fit this contract. Do not try to repair that format by removing every newline.

I inspect the producer's schema before writing the loader. Which fields are required, and what happens to rejected records? Keep a file identifier and line number so the producer can correct one record. The file extension is a hint. It is not an encoding test or a schema agreement.

Read Bytes and Decode UTF-8 Deliberately

OPENROWSET BULK reads a server-side path. The example reads the bytes with SINGLE_BLOB and stores them in a varchar column with a UTF-8 collation. Converting that column to nvarchar then decodes the text as UTF-8. A COLLATE clause on a converted expression did not do that in my test, and the accented and Hindi text came back garbled. The UTF-8 collation needs SQL Server 2019 or newer, and the ordinal split needs SQL Server 2022 or newer.

CREATE TABLE #Lines(LineNumber bigint PRIMARY KEY,RawLine nvarchar(max) NOT NULL);
CREATE TABLE #FileText(Body varchar(max) COLLATE Latin1_General_100_BIN2_UTF8 NOT NULL);
INSERT #FileText(Body)
SELECT BulkColumn
FROM OPENROWSET(BULK N'C:\Imports\events.jsonl',SINGLE_BLOB) AS file_data;
DECLARE @document nvarchar(max);
SELECT @document=CONVERT(nvarchar(max),Body) FROM #FileText;
IF LEFT(@document,1)=NCHAR(65279)
    SET @document=SUBSTRING(@document,2,DATALENGTH(@document)/2);
INSERT #Lines(LineNumber,RawLine)
SELECT ordinal,
       CASE WHEN RIGHT(value,1)=NCHAR(13)
            THEN LEFT(value,LEN(value)-1) ELSE value END
FROM STRING_SPLIT(@document,NCHAR(10),1)
WHERE DATALENGTH(value)>0;
DELETE #Lines WHERE LEN(RawLine)=0;
SELECT LineNumber,RawLine FROM #Lines ORDER BY LineNumber;

Replace the path with a reviewed file on the database server. The code handles LF and CRLF line endings and removes an optional Unicode byte-order mark after decoding. It ignores empty or space-only lines. Keep that policy explicit. An older STRING_SPLIT without ordinal output cannot supply trustworthy original line numbers by ordering its results afterward.

Keep File Permissions Narrow

The SQL Server execution and authentication context determines file access. A local path must exist on the server, and a share needs both share and filesystem access for the applicable identity. Remote Windows authentication can introduce delegation requirements. Test that path with the intended execution identity rather than with an administrator's desktop account.

The caller also needs the appropriate SQL bulk-operation and database permissions. Grant only the required rights. OPENROWSET BULK is a different path from arbitrary linked-provider queries, so do not enable unrelated server features reflexively. I verify the exact error and identity before changing permissions. A file loader does not need a master key to the file server.

Reject Invalid JSON Lines Before Parsing

ISJSON checks syntax, not the business schema. Use its OBJECT constraint on supported versions so an array or scalar does not pass as an event object. Save invalid lines separately. For parsing, guard the actual argument to OPENJSON with CASE. Do not rely only on a WHERE filter to guarantee which expression the optimizer evaluates first.

CREATE TABLE #Reject
(LineNumber bigint NOT NULL,Reason nvarchar(200) NOT NULL,RawLine nvarchar(max) NOT NULL);
INSERT #Reject
SELECT LineNumber,N'Not a valid JSON object',RawLine
FROM #Lines WHERE ISJSON(RawLine,OBJECT)<>1;
SELECT l.LineNumber,
       TRY_CONVERT(bigint,j.EventID) AS EventID,
       TRY_CONVERT(datetime2(3),j.OccurredAt,126) AS OccurredAt,
       TRY_CONVERT(decimal(18,2),j.Amount) AS Amount,
       j.Message
INTO #Parsed
FROM #Lines AS l
CROSS APPLY OPENJSON(CASE WHEN ISJSON(l.RawLine,OBJECT)=1
                         THEN l.RawLine ELSE N'{}' END)
WITH
(
    EventID nvarchar(max) '$.id',
    OccurredAt nvarchar(max) '$.occurredAt',
    Amount nvarchar(max) '$.amount',
    Message nvarchar(max) '$.message'
) AS j
WHERE ISJSON(l.RawLine,OBJECT)=1;

Run all staging blocks in the same session. The guard prevents a malformed object from aborting the entire parse. Keep the original line unchanged in the rejection record. Sanitizing the evidence can make the producer's error impossible to reproduce.

Convert Fields Without Losing the Batch

The WITH clause maps JSON paths to staging strings. TRY_CONVERT then produces typed values without throwing for a bad numeric or date field. Missing paths also produce NULL in lax mode. Decide which NULLs are allowed and reject required fields that are absent or unconvertible. A syntactically valid object can still be unusable data.

I compare the schema with the producer's actual payloads, including casing. JSON path matching is case-sensitive. A renamed field can become NULL without a syntax error. Choose strict validation where required, and version the input contract when fields change.

Insert Only Accepted Typed Rows

The example requires an event ID, timestamp, and amount. It separates rows with failed required-field conversion from accepted rows, then inserts into a typed table. The destination has a unique event key. Check duplicate IDs before insertion so the rejection policy covers them deliberately rather than letting a key violation abort the load.

INSERT #Reject
SELECT p.LineNumber,N'Missing or invalid required field',l.RawLine
FROM #Parsed AS p JOIN #Lines AS l ON l.LineNumber=p.LineNumber
WHERE p.EventID IS NULL OR p.OccurredAt IS NULL OR p.Amount IS NULL;
CREATE TABLE #Events
(EventID bigint PRIMARY KEY,OccurredAt datetime2(3) NOT NULL,
 Amount decimal(18,2) NOT NULL,Message nvarchar(max) NULL);
IF EXISTS
(SELECT EventID FROM #Parsed WHERE EventID IS NOT NULL
 GROUP BY EventID HAVING COUNT_BIG(*)>1)
    THROW 50000,'Duplicate event IDs require an explicit resolution.',1;
INSERT #Events(EventID,OccurredAt,Amount,Message)
SELECT EventID,OccurredAt,Amount,Message FROM #Parsed
WHERE EventID IS NOT NULL AND OccurredAt IS NOT NULL AND Amount IS NOT NULL;

Reconcile and Retain Provenance

Reconcile nonblank input lines against accepted rows and rejected rows. Store the source file identity and line ordinal with durable staging data. For a production table, choose a transaction, batch identifier, and retry rule. A rerun after a network timeout must not silently insert the same events again.

SELECT (SELECT COUNT_BIG(*) FROM #Lines) AS input_lines,
       (SELECT COUNT_BIG(*) FROM #Events) AS accepted_rows,
       (SELECT COUNT_BIG(*) FROM #Reject) AS rejected_rows;
SELECT LineNumber,Reason FROM #Reject ORDER BY LineNumber;

My test file had nine lines, including a blank line, a space-only line, an array, a broken object, a bad date and a missing amount. The loader kept seven lines, accepted three rows and rejected four. A load that throws on duplicate IDs has stopped before final reconciliation. Resolve that condition and rerun from preserved staging. Do not report success just because some preceding statements inserted rows. I keep rejection counts and reasons visible to the owner of the export.

Scale the JSON Lines Loader Without Guessing

Match the timestamp type to the input contract. This example expects UTC timestamps represented without an offset and stores datetime2. For offset-bearing input, use datetimeoffset and an explicit normalization rule. Do not let a conversion discard time-zone meaning quietly. Specify decimal scale as well. Values requiring more fractional precision need rejection or an agreed rounding policy.

Preserve extracted strings until validation finishes. A short staging field can truncate data before TRY_CONVERT sees it. Use wide staging values, then check lengths and destination rules explicitly. The typed destination should receive a value whose interpretation was accepted, rather than a convenient prefix of the source.

SINGLE_BLOB reads the whole file into one value, so large files need a tested memory and batching strategy. Partition exports into manageable files or use a supported bulk pipeline with a durable landing stage. Preserve the same schema and rejection rules across batches. Changing transport should not change data validation.

Test non-ASCII text, escaped quotes, CRLF and LF endings, invalid JSON, absent fields, numeric overflow, and repeated IDs. The goal is a typed load whose accepted and rejected records can both be explained. JSON Lines makes record boundaries convenient. The loader must make the meaning and outcome equally clear.

Related reading on this blog: 2016: Opening JSON with OPENJSON() and Loading Large Files Fast With BULK INSERT.

Valid JSON is not validated business data, it is the first gate before schema and type checks.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.