Converting BlobColumn to String in SSIS Script Component

Question

How do I convert BlobColumn to String in SSIS Script Component.

E.G:

Source Column : OrganisationProviderID NVARCHAR(MAX)

Destination Column : OrganisationProviderID VARCHAR(20)

How can this be acheived in SSIS Script Component?

Answer 1

Here is the function I use to convert BlobColumn to string in SSIS Script Components

string BlobColumnToString(BlobColumn blobColumn)
{
    if (blobColumn.IsNull)
        return string.Empty;

    var blobLength = Convert.ToInt32(blobColumn.Length);
    var blobData = blobColumn.GetBlobData(0, blobLength);
    var stringData = System.Text.Encoding.Unicode.GetString(blobData);

    return stringData;
}

Answer 2

Why are you using the script component for this? Also, i'm not sure that a NVARCHAR(MAX) really qualifies as a BLOB column.

If you want to do it as part of a data flow task, on your Data Source, set the data access mode to 'SQL Command' then use the following command text:

select left(OrganisationProviderID,20) as OrganisationProviderID
from src

Then link this to your destination component.