sql-server - 解析 SQL Server 集成服务包中的 JSON 数据？

我正在尝试设置一个 SSIS 作业，该作业将从 MailChimp 中提取 JSON 编码的邮件列表，将其与我们的 CRM 数据库 (SQL Server) 中的客户列表进行比较，并通过 JSON 上传任何尚不存在的新客户。除了编写脚本任务之外，我似乎无法在 SSIS 中找到有关序列化/反序列化 JSON 的任何内容，而且似乎我无法将 .Net 序列化库导入到脚本中。有什么建议么？提前致谢!

最佳答案

这里要解决的几件事:

首先，您在脚本组件中添加新库的问题。我假设您使用 VS 2008 进行 SSIS 开发，并希望使用 .net 3.5 库来执行此操作。你去项目，添加引用，你没有看到任何你需要的 dll。这可能部分是因为您使用的是 Windows 7 和紧凑型 3.5 框架。 .net 3.5.1 随 Windows 7 一起提供，您只需启用它。转到控制面板、程序和功能。在该屏幕中，您将看到打开或关闭 Windows 功能，单击它。在该窗口中检查 Microsoft .NET Framework 3.5.1，这种方式需要几分钟才能运行。完成后查找类似于这些 C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework.NETFramework\v3.5\Profile\Client 和 C:\Program Files (x86)\Reference Assemblies\Microsoft\的目录框架\v3.5。在这 2 个目录之间，您将找到 JSON 序列化/反序列化所需的任何 dll。这些可以通过转到 Project-->Add Reference-->Browse Tab 来添加到您的项目中，然后导航到 v3.5 目录并选择您需要的 dll(System.Web.Extensions.dll(v3.5.30729.5446) ) 在本例中使用)。

要从 Web 服务获取 JSON、对其进行反序列化并将数据发送到您的 CRM 数据库，您必须使用脚本组件作为数据流中的源，并将列添加到将用于保存数据的输出缓冲区中来自 JSON 提要(在输入和输出屏幕上)。在代码中，您需要覆盖 CreateNewOutputRows 方法。以下是如何执行此操作的示例:

说你的 JSON 看起来像这样... [{"CN":"ALL","IN":"Test1","CO":0,"CA":0,"AB":0},{"CN":"ALL","IN":"Test2","CO":1,"CA":1,"AB":0}]
我首先定义一个类来反射(reflect)这个 JSON 提要属性(以及您在输入和输出屏幕上定义的列)，一旦您反序列化，它最终将保存这些值......

class WorkGroupMetric
{
    public string CN { get; set; }

    public string IN { get; set; }

    public int CO { get; set; }

    public int CA { get; set; }

    public int AB { get; set; }
}

现在，您需要调用 Web 服务并使用 HttpWebRequest 和 Stream 获取 JSON 提要:

string wUrl = "YOUR WEB SERVICE URI";
string jsonString;
HttpWebRequest httpWReq = (HttpWebRequest)WebRequest.Create(wUrl);
HttpWebResponse httpWResp = (HttpWebResponse)httpWReq.GetResponse();
Stream responseStream = httpWResp.GetResponseStream();
using (StreamReader reader = new StreamReader(responseStream))
            {
                jsonString = reader.ReadToEnd();
                reader.Close();
            }

现在我们将 json 反序列化为一个 WorkGroupMetric 数组

JavaScriptSerializer sr = new JavaScriptSerializer();
WorkGroupMetric[] jsonResponse = sr.Deserialize<WorkGroupMetric[]>(jsonString);

反序列化后，我们现在可以将行输出到输出缓冲区:

 foreach (var metric in jsonResponse)
        {

            Output0Buffer.AddRow();
            Output0Buffer.CN = metric.CN;
            Output0Buffer.IN = metric.IN;
            Output0Buffer.CO = metric.CO;
            Output0Buffer.CA = metric.CA;
            Output0Buffer.AB = metric.AB;
        }

这是所有代码放在一起的样子(我有一个分步示例 here ):

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Net;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
using System.Web.Script.Serialization;


 [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
 public class ScriptMain : UserComponent
 {

public override void CreateNewOutputRows()
{
    string wUrl = "YOUR WEB SERVICE URI";

    try
    {
        WorkGroupMetric[] outPutMetrics = getWebServiceResult(wUrl);

        foreach (var metric in outPutMetrics)
        {

            Output0Buffer.AddRow();
            Output0Buffer.CN = metric.CN;
            Output0Buffer.IN = metric.IN;
            Output0Buffer.CO = metric.CO;
            Output0Buffer.CA = metric.CA;
            Output0Buffer.AB = metric.AB;
        }

    }
    catch (Exception e)
    {
        failComponent(e.ToString());
    }

}


private WorkGroupMetric[] getWebServiceResult(string wUrl)
{

    HttpWebRequest httpWReq = (HttpWebRequest)WebRequest.Create(wUrl);
    HttpWebResponse httpWResp = (HttpWebResponse)httpWReq.GetResponse();
    WorkGroupMetric[] jsonResponse = null;

    try
    {

        if (httpWResp.StatusCode == HttpStatusCode.OK)
        {

            Stream responseStream = httpWResp.GetResponseStream();
            string jsonString;

            using (StreamReader reader = new StreamReader(responseStream))
            {
                jsonString = reader.ReadToEnd();
                reader.Close();
            }

            JavaScriptSerializer sr = new JavaScriptSerializer();
            jsonResponse = sr.Deserialize<WorkGroupMetric[]>(jsonString);

        }

        else
        {
            failComponent(httpWResp.StatusCode.ToString());

        }
    }
    catch (Exception e)
    {
        failComponent(e.ToString());
    }
    return jsonResponse;

}

private void failComponent(string errorMsg)
{
    bool fail = false;
    IDTSComponentMetaData100 compMetadata = this.ComponentMetaData;
    compMetadata.FireError(1, "Error Getting Data From Webservice!", errorMsg, "", 0, out fail);

}
}
 class WorkGroupMetric
 {
public string CN { get; set; }

public string IN { get; set; }

public int CO { get; set; }

public int CA { get; set; }

public int AB { get; set; }
 }

这现在可以用作数据目标(您的 CRM 数据库)的输入。到达那里后，您可以使用 SQL 比较数据并查找不匹配项，将数据发送到另一个脚本组件进行序列化，并将您需要的任何更新发送回 Web 服务。

要么

您可以在脚本组件中执行所有操作，而不会将数据输出到输出缓冲区。在这种情况下，您仍然需要反序列化 JSON，但将数据放入某种集合中。然后使用 Entity Framework 和 LINQ 来查询您的数据库和集合。确定不匹配的内容，对其进行序列化，然后将其发送到同一脚本组件中的 Web 服务。

关于sql-server - 解析 SQL Server 集成服务包中的 JSON 数据？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/11677333/