Moving Data Out of SQL Server in Open Formats

A file is only useful when the receiving system can read it correctly. Open formats such as CSV, JSON, and Parquet each preserve different parts of your SQL Server data.

Define the Receiving Contract for Open Formats

Ask the receiving team what format, encoding, schema, and delivery method it accepts. Agree on column names, order, data types, time zone, NULL representation, and record boundary. An export is not finished when SQL Server writes a file. It is finished when the other side can load and validate it.

I begin with a small sample containing awkward values: commas, quotes, line breaks, Unicode, NULL, and dates. If the sample fails, the full export will fail with more confidence. Use production like data under the right privacy rules.

What will the receiver use as a key? Include that field explicitly. A file without stable identity is difficult to reconcile after a partial load or rerun.

CSV Is Simple Until a Value Is Not

CSV is widely accepted and easy to inspect. It is also only text. Delimiters inside values require quoting rules. A line break inside a text field can look like a new row to a weak parser. Agree on escaping, header row, encoding, and newline convention before exporting.

bcp can write delimited text from a table or query. It is fast and useful for bulk movement. It does not automatically make a complete CSV contract for every edge case. Test values containing commas and quotes against the receiver’s parser. If the format needs strict RFC style quoting, use a tool that implements it and verify the output.

I never call a file “CSV” solely because it ends in .csv. The delimiter and escaping rules are the real format.

Use bcp With a Repeatable Query

Put the export SELECT in a reviewed script. List columns explicitly and define a stable order when the file contract requires one. Record the database and query version. A SELECT star can add a new column after deployment and silently change the output shape.

Run bcp from a Windows host with the approved client utilities and permissions. Choose the correct character encoding and delimiter options. Use a secure authentication method rather than embedding a password in a command line. Capture the process exit code and output file details.

Validate the exported data by loading a sample into a separate target or comparing keys and counts through queries. Use actual results from your environment. A successful command exit is the beginning of validation.

JSON Carries Names and Structure

SQL Server can produce JSON with FOR JSON PATH. This is useful when the receiver wants named fields or nested records. JSON still needs a contract for dates, decimals, NULLs, and missing properties. A name in a JSON object does not guarantee the receiver will assign the same type.

Start with a small query. Inspect escaping for quotes and Unicode. Decide whether NULL fields should be included and document that choice. Keep the output shape stable across releases. A new property can be harmless to one consumer and break another.

I use JSON when the shape benefits from names or hierarchy. For a plain large table transfer, a columnar format or bulk tool can fit better.

SELECT
    name AS DatabaseName,
    database_id AS DatabaseId,
    create_date AS CreatedAt
FROM sys.databases
ORDER BY name
FOR JSON PATH, INCLUDE_NULL_VALUES;

Read JSON Back With a Schema

OPENJSON can parse JSON into rows. Use a WITH clause to declare expected types and paths. Without a schema, values are returned as generic text with type information that still needs interpretation. A typed read back is a practical contract test.

The example uses a small literal payload, so it runs without an external file. Compare the parsed values with the source values in your real export. Test missing and NULL properties too. Do not infer type preservation from one friendly sample.

If the receiver rejects a field, use its error and your typed test to find the mismatch. A date with a time zone and one without one can both look readable in a text editor.

DECLARE @payload nvarchar(max) =
N'[{"DatabaseName":"Sample","DatabaseId":7}]';
SELECT DatabaseName, DatabaseId
FROM OPENJSON(@payload)
WITH
(
    DatabaseName nvarchar(128) '$.DatabaseName',
    DatabaseId int '$.DatabaseId'
);

Parquet Keeps a Typed Columnar Shape

Parquet is a columnar file format with schema metadata. It suits analytics pipelines that read selected columns from large datasets. Choose an export pipeline or tool that can write the exact Parquet schema your receiver expects. SQL Server alone is not a promise that every installation has the same direct Parquet export path.

Check decimal precision, timestamps, time zone handling, binary data, and NULLs. A receiving engine can map a logical type differently from the export tool. Validate with the receiver’s actual reader, not only a local file preview.

I ask for a schema printout and a sample read after export. If the receiving team cannot read the sample, scaling the job will only produce a larger unreadable file.

Reconcile the Transfer in Open Formats

Agree on a snapshot boundary. A live table can change while the export runs. Use an approved isolation or extraction strategy and record when the data was captured. The receiver needs to know whether two files belong to the same logical cut.

Compare source and destination counts, keys, and selected aggregates using queries. Investigate differences from filtering, NULL conversion, duplicate keys, or truncated text. Do not fabricate a matching count in documentation. Save the actual validation output.

Retain the export job log, schema contract, file hash, and receiving acknowledgment according to your policy. Those artifacts make reruns safe and disputes easier to settle.

Plan Reruns of Open Formats Exports Before the First Run

Decide whether a rerun replaces a whole file, appends a new partition, or sends a correction. Give files stable naming and a unique batch identifier. The receiver should be able to detect a duplicate delivery without loading it twice.

Protect sensitive data in transit and at rest. Open formats are easy to move, which also makes them easy to mishandle. Limit access to the export folder and remove temporary copies after validation.

A durable export process has a clear query, format contract, validation, and rerun rule. With open formats, the file extension is the least interesting part of the design.

Related reading on this blog: Troubleshooting Common CSV Import Issues and Azure Data Studio- Export Any SQL SERVER Query As JSON.

An open format is not a successful export, it is a contract the receiving system can prove it read.

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