SSIS 脚本组件不允许文本流输出 [英] SSIS Script Component won't allow text Stream Output

问题描述

我正在尝试使用 SSIS 将 JSON 文件导入我的数据库.由于我使用的是 SQL Server 2016，我可以将文件放在一行中并使用 OPENJSON 读取它们.

I'm trying to import JSON files into my DB using SSIS. Since I'm on SQL Server 2016 I can bring the files in a single row and read them using OPENJSON.

我的问题是不想只引入 JSON 文本.我还需要从文件名和当前目录中获取一些信息.

My issue is don't want to just bring in the JSON text. I also want need to get some information from the filename and current directory.

我的脚本组件如下所示:

My script component looks something like this:

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    string type = "NA";
    string ChassisSN = "NA";
    string CartridgeSN = "NA";

    string filepath = Variables.File;
    string filename = Path.GetFileNameWithoutExtension(filepath);
    string filenamefull = Path.GetFileName(filepath);
    string Parent = new DirectoryInfo(Path.GetDirectoryName(filepath)).Name;
    Char splitDelim = '_';
    String[] FileNameSplit = filename.Split(splitDelim);
    String[] ParentSplit = Parent.Split(splitDelim);
    CartridgeSN = FileNameSplit[2];
    ChassisSN = ParentSplit[2];
    type = FileNameSplit[3];

    if (ChassisSN != oldChassisSN)
    {
        oldChassisSN = ChassisSN;
    }

    Output0Buffer.AddRow();
    Output0Buffer.Filename = filenamefull.Trim();
    Output0Buffer.ChassisSN = ChassisSN.Trim();
    Output0Buffer.CartridgeSN = CartridgeSN.Trim();
    Output0Buffer.Type = type.Trim();
    Output0Buffer.JSON = Row.Column0.ToString;
}

我的问题是当我尝试将 JSON 数据放回新列时，它说它是只读的，因为我选择了文本流数据类型.由于文件很大，字符很可能超过 4000.

My issue is when I try to put the JSON data back into a new column, it says its read only since I pick the Text Stream Data type. The characters will most likely be over a 4000 since the files are large.

我还尝试将平面文件源和脚本作为源并合并数据，但不断出现在不同的行上.

I also tried to do both a Flat File source and the Script as Source and merge the data, but the kept coming up on separate rows.

它循环的每个文件只产生一行数据，可以在我的代码中看到.那么我如何才能在一行中获得我需要的所有信息?

Each file it loops through only produces one row of data which can be seen in my code. So how can I get the information I need all on one row?

推荐答案

有点晚了，但可能仍然适用于寻找此问题答案的其他人.

A bit late, but might still be relevant for others looking for the answer to this question.

当您将输出列定义为 DT_(N)TEXT 时，它会从值类型变为 BlobColumn 对象，并且不能直接为其赋值.相反，应该使用 AddBlobData() 方法来提供一个值:

When you define an output column as DT_(N)TEXT, it turns from a value type to a BlobColumn object, and cannot have its value assigned directly. Instead, the AddBlobData() method should be used to provide a value:

MainBuffer.AddRow();
MainBuffer.Id = item.Id;
MainBuffer.LargeCol.AddBlobData(Encoding.UTF8.GetBytes(item.JsonData));

这篇关于SSIS 脚本组件不允许文本流输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！