Azure Data Factory动态映射JSON日期列至SQL数据库

核心思路 #

针对API返回的rates节点下动态日期键的JSON结构，我们可以通过SQL原生解析或ETL脚本组件两种方式实现映射，同时添加预定义整数列。

方案1：SQL Server OPENJSON 直接解析 #

1. 目标表结构（先创建） #

CREATE TABLE ExchangeRates (
    CustomCol1 INT,          -- 预定义整数列1
    CustomCol2 INT,          -- 预定义整数列2
    ExchangeDate DATE PRIMARY KEY,
    MXNExchangeRate DECIMAL(18,4)
);

2. 解析并插入数据 #

假设已通过程序或OPENROWSET获取到API返回的JSON内容并存入变量@json，执行以下SQL：

DECLARE @json NVARCHAR(MAX) = 'API返回的完整JSON内容';

INSERT INTO ExchangeRates (CustomCol1, CustomCol2, ExchangeDate, MXNExchangeRate)
SELECT
    100 AS CustomCol1,       -- 替换为你的预定义整数
    200 AS CustomCol2,       -- 替换为你的预定义整数
    CAST([Key] AS DATE) AS ExchangeDate,
    CAST(rate_value AS DECIMAL(18,4)) AS MXNExchangeRate
FROM
    OPENJSON(@json, '$.rates')
    WITH (
        rate_value DECIMAL(18,4) '$.MXN'
    ) AS rate_data;

• OPENJSON会自动将rates下的动态日期识别为[Key]，无需通配符配置
• 通过WITH子句直接提取对应汇率值

方案2：SSIS Script Component 作为数据源（ETL场景） #

如果使用SSIS且无法用Data Flow，可通过脚本组件实现解析：

1. 添加Script Component作为数据源
2. 在脚本编辑器中引用Newtonsoft.Json（需提前安装NuGet包）
3. 编写C#解析逻辑：

using Newtonsoft.Json.Linq;
using System.Net.Http;
using System.Collections.Generic;

public override void CreateNewOutputRows()
{
    // 1. 调用API获取JSON
    using (var client = new HttpClient())
    {
        var jsonStr = client.GetStringAsync("API请求地址").Result;
        var jsonObj = JObject.Parse(jsonStr);
        var ratesDict = jsonObj["rates"].ToObject<Dictionary<string, JObject>>();

        // 2. 遍历动态日期键并输出行
        foreach (var dateKvp in ratesDict)
        {
            Output0Buffer.AddRow();
            // 设置预定义整数列
            Output0Buffer.CustomCol1 = 100;
            Output0Buffer.CustomCol2 = 200;
            // 映射日期列
            Output0Buffer.ExchangeDate = DateTime.Parse(dateKvp.Key);
            // 映射汇率值
            Output0Buffer.MXNExchangeRate = dateKvp.Value["MXN"].Value<decimal>();
        }
    }
}

4. 将脚本组件的输出连接到SQL目标组件，完成数据写入

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