c# - C# SSIS 中的 JSON 序列化

Question

已解决:我不再像处理数组一样处理字符串，而是向连接管理器添加了一个换行分隔符，这样每一行都被视为一个新行。这已经解决了问题并消除了通过 C# 脚本处理此问题的需要。

我构建了一个 SSIS 包，它使用 C# 脚本任务反序列化 JSON 字符串并将它们插入到表中。

我已经使用原型(prototype) JSON 字符串进行了此操作:

{"name":"Test 1","code":"398057008","table":"SNOMEDCT","addedby":"morgan.baxter","dateadded":1544523489235,"qualifier":[{"name":"Qualifier","value":"Confirmed Diagnosis","code":"410605003","prefix":"[C] "}],"prefix":"[C] "}

但是当我尝试向 JSON 字符串添加第二个项目时，我收到一个错误:

[{"name":"Test 2","code":"398057008","table":"SNOMEDCT","addedby":"morgan.baxter","dateadded":1544523489235,"qualifier":[{"name":"Qualifier","value":"Confirmed Diagnosis","code":"410605003","prefix":"[C] "}],"prefix":"[C] "},{"name":"Test 2","code":"44255352","table":"SNOMEDCT","addedby":"morgan.baxter","dateadded":1544523489235,"qualifier":[{"name":"Qualifier","value":"Confirmed Diagnosis","code":"53252355","prefix":"[C] "}],"prefix":"[C] "}]

错误:

Type 'SC_8aae662509ae4bab8491323924579173.Diagnosis' is not supported for deserialization of an array.

据我所知，这只是说我的“解析器”本质上不能支持字符串中的 JSON 字段数组。

这是我的主要代码块:

#region Namespaces
using System;
using System.Data;
using System.Collections.Generic;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Web.Script.Serialization;
using Microsoft.SqlServer.Dts.Pipeline;

namespace SC_8aae662509ae4bab8491323924579173
#endregion
{

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

        ///<param name="Row">The row that is currently passing through the component</param>;
        public override void Input0_ProcessInputRow(Input0Buffer Row)
        {
            JavaScriptSerializer js = new JavaScriptSerializer();

            // Give the input column a variable to make it easier to reference.
            BlobColumn combinedColumn = Row.Column0;

            // Convert from blob to string
            string reviewConverted = System.Text.Encoding.ASCII.GetString(combinedColumn.GetBlobData(0, Convert.ToInt32(combinedColumn.Length)));

            Diagnosis diagnosis = new Diagnosis();

            // Deserialize the string
            diagnosis = js.Deserialize<Diagnosis>(reviewConverted);

            // Assign values to output columns
            Row.name = diagnosis.name;
            Row.code = diagnosis.code;
            Row.table = diagnosis.table;
            Row.addedby = diagnosis.addedby;
            Row.dateadded = diagnosis.dateadded;

            Row.qualifierName = diagnosis.Qualifier[0].name;
            Row.qualifierValue = diagnosis.Qualifier[0].value;
            Row.qualifierCode = diagnosis.Qualifier[0].code;
            Row.qualifierPrefix = diagnosis.Qualifier[0].prefix;           

            if (diagnosis.Qualifier.Length == 2)
            {
                Row.lrName = diagnosis.Qualifier[1].name;
                Row.lrValue = diagnosis.Qualifier[1].value;
                Row.lrCode = diagnosis.Qualifier[1].code;
                Row.lrSuffix = diagnosis.Qualifier[1].prefix;
            }

            Row.jsonString = reviewConverted;

            Row.prefix = diagnosis.prefix;
            Row.suffix = diagnosis.suffix;

        }
    }
}

限定符类:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SC_8aae662509ae4bab8491323924579173
{
    class qualifier
    {
        public string name { get; set; }
        public string value { get; set; }
        public string code { get; set; }
        public string prefix { get; set; }
    }
}

诊断类:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SC_8aae662509ae4bab8491323924579173
{
    class Diagnosis
    {
        public string name { get; set; }
        public string code { get; set; }
        public string table { get; set; }
        public string addedby { get; set; }
        public string dateadded { get; set; }
        public qualifier[] Qualifier { get; set; }
        public string prefix { get; set; }
        public string suffix { get; set; }
        public string jsonString { get; set; }
    }
}

我尝试使用数组和我的 if 函数来满足不止一种诊断，但无济于事。有任何想法吗？非常感谢。

Answer 1

你说，你传递数组而不是 json 中的对象。那么，为什么要继续尝试反序列化为 Diagnosis 对象呢？你真的应该在这里使用 Collection 和你的类型诊断:

// Deserialize the string
var diagnosisCollection = js.Deserialize<ICollection<Diagnosis>>(reviewConverted);

代替旧的

// Deserialize the string
diagnosis = js.Deserialize<Diagnosis>(reviewConverted);

然后使用 foreach 为输出列分配值

foreach (var diagnosis in diagnosisCollection ) {
    // do stuff with your buffer here

}