SSIS Script Component won't allow text Stream Output

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?

You're likely looking for the [ImportColumn](https://stackoverflow.com/a/12825565/181965) feature for SSIS Let me know if it's not clear, been quite some time since I revisited the topic — billinkc, Feb 20 '18 at 23:09
I believe I found a solution but its still not working correctly. I can assign the JSON data to variable, since it can hold more than 4000 characters. My only issue is in the post execute its not assigning the variable until after the first row has been imported, cause a mismatch between json data and filename information. — AlanPear, Feb 21 '18 at 15:41

score 7 · Accepted Answer · answered Oct 22 '18 at 00:33

7

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

answered Oct 22 '18 at 00:33

Roger Wolf

7,307
2
24
33

What is MainBuffer? The question used Output0Buffer and doesn't reference 'item', so I'm getting confused trying to match the question to the answer. – thursdaysgeek Aug 07 '19 at 23:35
@thursdaysgeek, it's the same as `Output0Buffer` in the question; I just copied this piece from my code, where I have renamed it (although I don't remember how). – Roger Wolf Aug 08 '19 at 11:41
I'm trying to do this in ssis 2012 and am getting errors: your answer seems to be what I need, but I just can't get it to compile. Could you look at https://stackoverflow.com/questions/57381226/transform-dt-text-in-ssis-2012-for-oracle-clob? – thursdaysgeek Aug 08 '19 at 15:13

SSIS Script Component won't allow text Stream Output

1 Answers1

Linked