在Azure Data Factory中实现任意分隔文件到统一JSON结构或Azure SQL数据库的通用导入加载方案咨询

Absolutely, this is totally doable in Azure Data Factory (ADF) — and there are a couple of solid approaches to pull off this "know-nothing" import routine you're describing. Let's break down how to make this work for both your desired JSON output and direct loading to Azure SQL Database.

Core Concepts to Make This Work #

The key here is handling dynamic metadata (since we don't know column names upfront) and transforming wide delimited tables into your narrow, uniform Row | Field | DataValue structure. Here's the step-by-step breakdown:

1. Dynamically Read Any Delimited File #

First, we need to reliably read arbitrary delimited files without hardcoding column names or separators:

• Use ADF's Delimited Text Dataset: Enable the "First row as header" setting, and set the delimiter to "Detect automatically" (ADF will sniff out common separators like pipes, commas, tabs). For full control, you can also add a pipeline parameter for the delimiter to let users specify it if needed.
• Add a Get Metadata Activity to your pipeline: Configure it to point to your input file, and check the "Column names" option in the "Field List" settings. This will fetch all column names from the file's header row, which we'll use later for dynamic transformation.

2. Transform to the Uniform Structure with Data Flow #

ADF Data Flow is perfect for this dynamic transformation — it supports working with unknown columns at design time:

1. Add a Data Flow activity to your pipeline, and pass the column names from the Get Metadata activity into it as a parameter (e.g., @activity('Get Metadata').output.columnNames).
2. Source in Data Flow: Connect to your Delimited Text Dataset, and ensure it's set to read the header row.
3. Add a Derived Column: Create a RowNum column using the expression rowNumber() + 1 (since rowNumber() starts at 0, this ensures your first data row is labeled as Row 1).
4. Dynamic Unpivot Transformation: This is the magic step to turn wide columns into rows:
   • In the Unpivot settings, set the "Unpivot key" to Field (this will hold your column names) and the "Value column" to DataValue (this will hold the cell values).
   • For the "Pivot columns", use the dynamic column list parameter you passed in (e.g., @split(pipeline().parameters.columnList, ',')). This tells ADF to unpivot every column from the input file.
5. Add FileName Metadata: Use another Derived Column to add a FileName field, populated either by a pipeline parameter (@pipeline().parameters.fileName) or directly from the source file's metadata (@metadata().fileName).

3. Output to Your Target #

You can now route the transformed data to either JSON or Azure SQL Database:

Option 1: Output to the Required JSON Structure

• Use a JSON Sink in Data Flow. Configure it to output as an array, and use a Derived Column or Select transformation to structure the final output:
  • Create a root object with filename (your FileName column) and Data (an array containing the RowNum, Field, DataValue rows).
• Alternatively, you can use a Copy Activity with dynamic mapping if you prefer, but Data Flow gives you more control over the JSON structure.

Option 2: Direct Load to Azure SQL Database

• This is even simpler: Use an Azure SQL Database Sink in Data Flow, and map the transformed columns (FileName, RowNum, Field, DataValue) directly to your heap table's columns. ADF will handle writing the rows without any extra formatting.

Example Walkthrough (Using Your EMPS2024 File) #

Let's apply this to your sample pipe-delimited file:

1. Get Metadata fetches column names: EMPID, EMPNAME, STARTDT, ENDDT, SALARY.
2. Data Flow reads the file, adds RowNum (1, 2, 3 for your data rows).
3. Unpivot turns each column into rows: e.g., Row 1 gets 5 entries (one for each column, with Field set to the column name and DataValue set to the cell value).
4. Add FileName = 'EMPS2024'.
5. Output to JSON gives exactly the structure you shared, or write directly to your Azure SQL heap table.

Key Notes & Edge Cases #

• Handling "NULL" Strings: Your sample uses the string "NULL" instead of actual null values. ADF will preserve this as-is unless you add a Derived Column to convert it to a real null if needed.
• Large Files: For big datasets, enable partitioning in Data Flow to optimize performance.
• Reusability: Turn this into a reusable pipeline by parameterizing inputs (file path, delimiter, target table/JSON path) so you can run it for any delimited file without modifying the pipeline.

Compared to your existing Adeptia solution, ADF offers native integration with Azure services, so you won't need extra tools to connect to Azure SQL Database or store files in Azure Storage.

备注：内容来源于stack exchange，提问作者Joel Ehrlich