SSIS Script Component won't allow text Stream

Question

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.

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;
}

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?

Answer 1

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

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));