SSIS Script Component: Microsoft.SqlServer.Dts.Pipeline.BlobColumn

Question

Struggling with a C# Component. What I am trying to do is take a column that is ntext in my input source which is delimited with pipes, and then write the array to a text file. When I run my component my output looks like this:

DealerID,StockNumber,Option
161552,P1427,Microsoft.SqlServer.Dts.Pipeline.BlobColumn

Ive been working with the GetBlobData method and im struggling with it. Any help with be greatly appreciated! Here is the full script:

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    string vehicleoptionsdelimited = Row.Options.ToString();
    //string OptionBlob = Row.Options.GetBlobData(int ;
    //string vehicleoptionsdelimited = System.Text.Encoding.GetEncoding(Row.Options.ColumnInfo.CodePage).GetChars(OptionBlob);
    string[] option = vehicleoptionsdelimited.Split('|');
    string path = @"C:\Users\User\Desktop\Local_DS_CSVs\";

    string[] headerline =
    {
        "DealerID" + "," + "StockNumber" + "," + "Option"
    };

    System.IO.File.WriteAllLines(path + "OptionInput.txt", headerline);

    using (System.IO.StreamWriter file = new System.IO.StreamWriter(path + "OptionInput.txt", true))
    {
        foreach (string s in option)
        {
            file.WriteLine(Row.DealerID.ToString() + "," + Row.StockNumber.ToString() + "," + s);
        }
    }

Answer 1

Try using

BlobToString(Row.Options)

using this function:

  private string BlobToString(BlobColumn blob)
    {
        string result = "";
        try
        {
            if (blob != null)
            {
                result = System.Text.Encoding.Unicode.GetString(blob.GetBlobData(0, Convert.ToInt32(blob.Length)));
            }
        }
        catch (Exception ex)
        {
            result = ex.Message;
        }
        return result;
    }

Adapted from: http://mscrmtech.com/201001257/converting-microsoftsqlserverdtspipelineblobcolumn-to-string-in-ssis-using-c

Answer 2

Another very easy solution to this problem, because it is a total PITA, is to route the error output to a derived column component and cast your blob data to a to a STR or WSTR as a new column.

Route the output of that to your script component and the data will come in as an additional column on the pipeline ready for you to parse.

This will probably only work if your data is less than 8000 characters long.