如何将SSIS脚本组件获取的API数据写入OutputBuffer对接OLE DB目标

前置问题修正 #

你提供的现有代码存在几处会直接导致编译失败、运行异常的问题，需要先修正：

• 重复定义同名变量requestObject：第一次声明为WebRequest类型，后续试图将HttpWebResponse对象存入同名变量，会触发类型不兼容的编译错误
• 未显式声明responseObject变量
• 未正确释放HTTP响应流资源，长期运行会出现网络连接泄漏
• 针对公开测试API的冗余认证配置可以直接删除，该接口不需要内置网络凭据即可访问

实现步骤 #

1. 添加JSON解析依赖 #

接口返回的是由多条todo对象组成的JSON数组，不能通过手动字符串切割的方式提取字段，需要引入正规JSON解析库：

• 打开SSIS脚本组件的代码编辑界面，在右侧解决方案资源管理器右键项目，选择【添加引用】
• 在程序集列表中找到Newtonsoft.Json，勾选确认引入；如果列表中未找到，可通过NuGet包管理器将其安装到当前脚本项目
• 在代码头部的命名空间引入区添加：

  using Newtonsoft.Json.Linq;
  

2. 完整可运行的写入逻辑 #

修正API调用代码后，逐行解析JSON数组、通过OutputBuffer.AddRow()写入输出列，完整代码如下：

public override void CreateNewOutputRows()
{
   // 配置API请求
   string url = "https://jsonplaceholder.typicode.com/todos";
   WebRequest requestObject = WebRequest.Create(url);
   requestObject.Method = "GET";

   string result = null;
   // 用using块自动释放资源
   using (HttpWebResponse responseObject = (HttpWebResponse)requestObject.GetResponse())
   using (Stream stream = responseObject.GetResponse())
   using (StreamReader sr = new StreamReader(stream))
   {
      result = sr.ReadToEnd();
   }

   // 解析返回的JSON数组
   JArray todoCollection = JArray.Parse(result);
   
   // 遍历每一条数据
   foreach (JObject todoItem in todoCollection.Children<JObject>())
   {
       // 每处理一条数据，调用一次AddRow创建新的输出行
       OutputBuffer.AddRow();
       
       // 按已配置的DT_STR列类型，将字段值转成字符串后赋值
       OutputBuffer.userId = todoItem["userId"].ToString();
       OutputBuffer.Id = todoItem["id"].ToString();
       OutputBuffer.title = todoItem["title"].ToString();
       OutputBuffer.completed = todoItem["completed"].ToString();
   }
}

注意事项 #

• 赋值时的输出列名称必须和你在脚本组件编辑器里配置的列名完全一致，大小写不匹配会触发编译报错
• 因为你当前设置的输出列全部为DT_STR类型，不需要额外把userId、id转成整数、completed转成布尔值，直接转成字符串即可完成类型匹配，后续映射到OLE DB目标时，再根据目标表的字段类型做隐式转换即可
• 不要用正则匹配、字符串截取的方式提取JSON字段，遇到字段值含转义符、引号、特殊格式时会直接导致解析错误
• 后续如果调用需要域认证的内部API，再将之前注释的凭据配置（UserDefaultCredentials、PreAuthenticate、Credentials属性设置）加回请求配置即可

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