如何借助SSIS与OPENJSON高效将JSON平面文件导入SQL Server 2016？

Absolutely—combining SSIS with SQL Server 2016's OPENJSON is a fantastic way to speed up your JSON processing workflow, and it’s way more efficient than relying solely on Script Components for this task. Your current pain point makes total sense: Script Components process data line-by-line via .NET code, which becomes a bottleneck when dealing with dozens of files. OPENJSON leverages SQL Server's native query optimization and batch processing capabilities to cut down on that 15-minute runtime drastically.

Here's a step-by-step implementation tailored to SQL Server 2016:

1. Core Approach #

We’ll use SSIS's Foreach Loop Container to iterate through all JSON files, pass each file path to a SQL statement, read the file content via OPENROWSET(BULK...), parse it with OPENJSON, and insert the structured data directly into your target table—all within the SQL engine for maximum efficiency.

2. Step-by-Step Implementation #

Step 1: Set Up SSIS Variables #

First, create a couple of variables to manage file paths and SQL parameters:

• @FilePath (String): Stores the full path of the current JSON file being processed
• @SQLCommand (String, optional): Holds the dynamic SQL for parsing (you can also write parameterized queries directly in the Execute SQL Task)

Step 2: Configure the Foreach Loop Container #

• Add a Foreach Loop Container to your control flow
• Set the enumerator to Foreach File Enumerator, point it to your JSON folder, and set the file filter to *.json
• In the "Variable Mappings" tab, map the current file path to your @FilePath variable

Step 3: Execute SQL Task for Parsing & Insertion #

Inside the Foreach Loop, add an Execute SQL Task with these settings:

• Use your SQL Server 2016 connection manager
• Set the SQL statement type to "Direct Input" and paste in this parameterized query (adjust the target table and JSON schema to match your data):

DECLARE @JsonContent NVARCHAR(MAX)
SELECT @JsonContent = BulkColumn
FROM OPENROWSET(BULK ? , SINGLE_CLOB) AS JsonFile

INSERT INTO YourTargetTable (Id, Name, CreatedDate)
SELECT Id, Name, CreatedDate
FROM OPENJSON(@JsonContent)
WITH (
    Id INT '$.user.id',
    Name VARCHAR(100) '$.user.name',
    CreatedDate DATETIME '$.metadata.created'
)

• Go to the "Parameter Mapping" tab, map your @FilePath variable to the ? placeholder (set direction to "Input" and data type to NVARCHAR)

Step 4: Additional Optimization Tips #

• Batch multiple files: If your JSON files have identical structures, consider merging them into a single file (via PowerShell/CMD) before parsing to reduce SQL execution overhead
• Define explicit schemas: Always use the WITH clause in OPENJSON to specify column types and paths—avoid letting SQL auto-infer types, as this slows down parsing
• Optimize target tables: Disable non-clustered indexes during bulk inserts (rebuild them afterward) or use the TABLOCK hint to speed up data insertion
• Reduce transaction overhead: Group multiple file inserts into a single transaction if your business rules allow, to minimize log write operations

3. Why This Beats Script Components #

• OPENJSON is a native SQL engine feature, so it uses parallel processing and query optimization that .NET line-by-line parsing can’t match
• It cuts down on data transfer between SSIS and the database: files are read and parsed directly in SQL, no need to load JSON content into SSIS memory first
• Batch operations have far lower overhead than processing each file individually with a Script Component

内容的提问来源于stack exchange，提问作者AlanPear