在SQL Server集成服务包中解析JSON数据?

I'm trying to set up an SSIS job that will pull a JSON-encoded mailing list from MailChimp, compare it to a list of customers in our CRM database (SQL Server), and upload via JSON any new customers not already there. I can't seem to find anything on serializing/deserializing JSON within SSIS, other than writing a script task, and it seems that I can't import the .Net serialization libraries into a script. Any suggestions? Thanks in advance!

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

1 个解决方案

#1

Couple things to address here:

这里有几点需要注意:

First, your problem with adding new libraries in the scripting component. I assume you're using VS 2008 to do your SSIS development and want to use the .net 3.5 library to do this. You go to project, add reference and you don't see any of the dll's you need. This may be in part that you're using windows 7 and the compact 3.5 framework. .net 3.5.1 comes with Windows 7, you just have to enable it. Go to control panel, programs and features. In that screen you will see Turn Windows features on or off, click on that. In that window check Microsoft .NET Framework 3.5.1, this way take a few minutes to run. Once it finishes look for a directory similar to these C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework.NETFramework\v3.5\Profile\Client and C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\v3.5. Between these 2 directories, you will find any dll you will need for serialization/deserializtion of JSON. These can be added to your project by going to Project-->Add Reference-->Browse Tab, then navigate to the v3.5 directory and select the dlls you need(System.Web.Extensions.dll(v3.5.30729.5446)is used in this example).

首先，在脚本组件中添加新库的问题。我假设您正在使用VS 2008来完成您的SSIS开发，并希望使用.net 3.5库来实现这一点。你进入项目，添加引用，你看不到任何你需要的dll。这可能是因为你正在使用windows 7和紧凑的3.5框架。进入控制面板，程序和特性。在那个屏幕上你会看到打开或关闭窗口的功能，点击那个。在这个窗口中，检查一下Microsoft . net Framework 3.5.1，这样运行需要几分钟。一旦它完成寻找类似这些C:\Program Files (x86)\Reference组件的目录\微软\ Framework.NETFramework \ v3.5 \概要\客户机和C:\Program Files (x86)\Reference总成\ Microsoft \ Framework \ v3.5。在这两个目录之间，您将找到JSON的序列化/反序列化所需的任何dll。可以通过进入project——>Add Reference——>Browse选项卡，然后导航到v3.5目录并选择所需的dll(system . web . extension .dll .dll .dll .dll .dll(在本例中使用v3.30729.5446)，将这些内容添加到项目中。

To get JSON from a web service, deserialize it, and send the data to your CRM database, you will have to use a script component as a source in your data flow and add columns to your output buffer that will be used to hold the data coming from the JSON feed(on the Input and Output screen). In the code, you will need to override the CreateNewOutputRows method. Here is an example of how to do this:

从web服务获取JSON,反序列化,并发送数据到CRM数据库,您将需要使用一个脚本组件来源你的数据流并将列添加到输出缓冲区,用于保存数据的JSON提要(屏幕上输入和输出)。在代码中，您需要重写CreateNewOutputRows方法。下面是一个如何做到这一点的例子:

Say Your JSON looked like this...[{"CN":"ALL","IN":"Test1","CO":0,"CA":0,"AB":0},{"CN":"ALL","IN":"Test2","CO":1,"CA":1,"AB":0}]

说你JSON这样…[{“CN”:“所有”,“在”:“Test1”、“有限公司”:0,“CA”:0,“AB”:0 },{“CN”:“所有”,“在”:“Test2”,“公司”:1、“CA”:1、“AB”:0 }]

I would fist define a class to mirror this JSON feed attributes (and the columns you defined on the inputs and outputs screen) that will eventually hold these values once you deserialize...as such:

我将首先定义一个类来镜像这个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; }
}

Now you need to call your web service and get the JSON feed using an HttpWebRequest and a Stream:

现在需要调用web服务并使用HttpWebRequest和流获取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();
            }

Now we deserialize our json into an array of WorkGroupMetric

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

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

After deserializing, we can now output the rows to the output buffer:

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

 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 is what all the code put together would look like(I have a step by step example 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; }
 }

This can now be used as an input for a data destination (your CRM database). Once there you can use SQL to compare the data and find mismatches, send the data to another script component to serialize, and send any updates you need back to the web service.

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

OR

或

You can do everything in the script component and not output data to the output buffer. In this situation you would still need to deserialze the JSON, but put the data into some sort of collection. Then use the entity framework and LINQ to query your database and the collection. Determine what doesn't match, serialize it, and send that to the web service in the same script component.

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