SSIS Script Component - Process 1 Row of Json to Table

Question

I'm new to SSIS. I'm trying get a field that contains a Json string from a table as the input, and convert the Json into a table as the output. I have setup an Asynchronous Script Component in SSIS, selected the Json field as input column, and also created output columns, but I came across a few problems:

1. How can I get the value from the input? I try to extract the input column to string in the "ProcessInputRow" method like

   string = Row.MData I get "Cannot implicitly convert type 'Microsoft.SqlServer.Dts.Pipeline.BlobColumn' to 'string'"

   Or return "Microsoft.SqlServer.Dts.Pipeline.BlobColumn" when I try to convert it to string

2. Should I just write my code in "ProcessInputRow" because I can use OutputBuffer.AddRows to construct my output? And ignore "CreateNewOutputRows" as I cannot access the input data in "CreateNewOutputRows"?

3. Or any suggestion of better approach of doing this?

Answer 1

I suspect you need to use code similar to this inside your script transform:

var myString = System.Text.Encoding.Unicode.GetString(Row.MData.GetBlobData(0,Convert.ToInt32(Row.MData.Length)));

this should allow you to get the json stored in a blob as a string you my need to substitute Ascii for unicode depending on the specifics of your code.