使用 SSIS 导入 CMS Open Payments API 年度数据
结论
SSIS 可以调用 CMS Open Payments API，但通常无法直接使用普通的 SSIS 数据源组件完成。常见做法是通过 Script Component、Script Task 或第三方 REST Source 组件发送 HTTP 请求，再解析 API 返回的 JSON 或 CSV 数据。
如果每年只做一次全量加载，通常可以按以下顺序选择：
- CMS 提供的官方 CSV 或其他批量下载文件；
- 通过 API 分页导入；
- 导入 Excel 文件。
对于完整的年度数据，批量下载文件一般更简单、稳定，也便于核对记录数。API 更适合自动化和增量更新，也可以筛选字段或只获取部分数据。Excel 通常放在最后考虑，因为它有行数限制，还容易受到类型推断和驱动依赖的影响。
为什么 SSIS 不能直接把 API 当作普通数据源
SSIS 自带的 Web Service Task 主要用于 SOAP/WSDL，并不是通用的 REST/JSON 数据源。如果 Open Payments API 返回 JSON，SSIS 还需要处理以下工作：
- 添加请求参数和必要的请求头；
- 处理分页；
- 解析 JSON；
- 将 API 字段映射到 SSIS 输出列；
- 处理限流、超时和重试；
- 将文本、日期和金额转换为数据库类型。
所以，能够导入并不意味着填入一个 URL 就能完成。首次开发时，API 方案通常比导入结构固定的 CSV 文件更费工。
具体端点、分页参数和身份验证要求，应以 CMS 当前的开发者页面为准。不同数据集可能使用不同的数据集标识和字段结构，不能假定所有年度共用同一个 URL。
推荐的实施方式
方案一：下载官方批量文件后导入
如果 CMS 提供 CSV、ZIP 或类似的年度批量文件，建议优先使用，不要先选 Excel。
基本流程如下：
- 下载对应年度的数据文件；
- 解压到固定的落地目录；
- 使用 Flat File Source 读取 CSV；
- 先将数据装载到 staging 表；
- 检查记录数、空值、重复记录和日期范围；
- 完成转换后写入正式表。
对于 staging 表中不稳定的字段，可以先定义为 nvarchar，确认实际数据后再转换成 date、decimal 或其他目标类型。这样能避免 SSIS 根据文件前几行错误推断数据类型。
使用 Excel 还可能遇到这些问题：
- 单个工作表最多容纳 1,048,576 行；
- ACE/OLE DB 驱动存在 32 位和 64 位不匹配的问题；
- 混合类型列可能被错误识别；
- 前导零可能丢失；
- 金额、日期和长文本可能被自动转换。
即使采用文件导入，也不建议把 Excel 作为首选格式。
方案二：使用 API 分页导入
如果需要自动化，或者 API 支持只获取指定年度和所需字段，可以在 Data Flow 中创建 Script Component，并将其设置为 Source。
下面的代码展示了基本模式。示例假定接口使用 $limit 和 $offset 分页，并返回 JSON 数组。这类参数常见于 Socrata/SODA 风格的 API，但实际使用时必须根据 CMS 当前的接口文档调整。
using System;
using System.Net.Http;
using Newtonsoft.Json.Linq;
public override void CreateNewOutputRows()
{
const int pageSize = 5000;
int offset = 0;
using (var client = new HttpClient())
{
client.Timeout = TimeSpan.FromMinutes(5);
client.DefaultRequestHeaders.UserAgent.ParseAdd("SSIS-OpenPayments-Import/1.0");
while (true)
{
string url =
Variables.ApiBaseUrl +
"?$limit=" + pageSize +
"&$offset=" + offset;
string json = client
.GetStringAsync(url)
.GetAwaiter()
.GetResult();
JArray records = JArray.Parse(json);
if (records.Count == 0)
break;
foreach (JObject record in records)
{
Output0Buffer.AddRow();
// 字段名仅为映射方式示例，必须替换成数据集的真实字段。
Output0Buffer.RecordId =
(string)record["record_id"] ?? String.Empty;
Output0Buffer.PaymentAmount =
(decimal?)record["payment_amount"] ?? 0M;
Output0Buffer.RawJson =
record.ToString(Newtonsoft.Json.Formatting.None);
}
if (records.Count < pageSize)
break;
offset += pageSize;
}
}
}Script Component 需要完成以下配置：
- 将
ApiBaseUrl添加为只读变量； - 引用
System.Net.Http； - 引用与 SSIS 脚本运行环境兼容的
Newtonsoft.Json； - 创建与代码一致的输出列；
- 根据 API 的实际字段调整数据类型和空值处理。
不要直接照搬示例中的字段名。首次请求时，可以先将每条记录的原始 JSON 保存到 staging 表的 nvarchar(max) 列中，再根据真实结构设计正式映射。这样也能更好地应对年度之间的字段变化。
更稳妥的生产结构
无论使用 API 还是文件，都建议分两个阶段加载：
CMS 数据源
→ 原始落地文件或 Raw JSON
→ staging 表
→ 校验和类型转换
→ 正式业务表
不要让 API 返回的数据直接覆盖正式表。应保留原始响应或原始文件，以便在出现字段变化、类型错误或数据遗漏时重新处理，无须再次依赖外部网站。
每次加载至少应记录以下信息：
- 数据年度；
- 数据集名称或标识；
- 下载或请求时间；
- 请求 URL；
- 文件校验值；
- API 页数；
- 原始记录数；
- 成功、拒绝和重复记录数；
- SSIS 包版本。
API 方案需要特别处理的问题
分页顺序
只使用 $offset 分页时，最好同时指定一个稳定且唯一的排序字段。例如：
?$limit=5000&$offset=0&$order=record_id
如果加载期间源数据发生变化，缺少稳定排序可能造成数据重复或遗漏。排序字段和分页语法仍须以实际 API 文档为准。
限流和重试
API 可能会限制请求频率或单次返回的记录数。遇到 HTTP 429、500、502、503 和 504 时，应设置有限次数的延迟重试，并记录最终失败的页码，不要无限重试。
数据类型
API 中的金额和日期有时会以字符串形式返回。转换时应明确指定文化区域。例如，金额可以使用：
decimal.TryParse(
value,
System.Globalization.NumberStyles.Number,
System.Globalization.CultureInfo.InvariantCulture,
out decimal amount
);缺失字段、空字符串和 JSON null 应写入数据库 NULL，不要全部转换成零或空字符串，否则会改变数据原本的含义。
字段变化
不同年度的数据集可能会增加、删除或重命名字段。正式运行前，应比较当前年度与上一年度的字段清单。如果关键字段缺失，应让包直接失败，避免静默写入错误数据。
最终选择建议
如果目标是每年完整导入一次，应优先查找 CMS 提供的官方 CSV 或 ZIP 批量下载文件。这类文件比 Excel 更适合 SSIS，也比逐页调用 API 更便于审计和重跑。
如果只能在 API 和 Excel 之间选择：
- 如果希望流程长期自动运行，并且能够承担首次开发成本，可以选择 API；
- 如果数据量不大，流程每年只运行一次，而且希望尽快完成首次加载，下载文件会更省事；
- 如果数据超过 Excel 的行数限制，或者经常出现类型识别错误，就不要使用 Excel，改用 API 或 CSV。
首次加载时，可以先完成一个年度的 staging 导入和质量核对，再决定是否将 API 调用封装为长期运行的 SSIS 包。这样能先确认数据规模、字段稳定性和分页规则，避免过早在 REST 调用细节上投入太多工作。
备注：内容仅供参考。
