How do I convert a String to a BlobColumn in SSIS

Question

So I have a column in my SSIS Script Called Data which is of type BlobColumn

I want to assign this column to a string value.

I do the following:

Row.Data = "MyString";

but I get the following error:

Cannot implicitly convert type 'string' to Microsoft.SqlServer.Dts.Pipeline.BlobColumn

So how do I assign a BlobColumn to a String value?

Possible duplicate of [Converting a string to byte-array without using an encoding (byte-by-byte)](http://stackoverflow.com/questions/472906/converting-a-string-to-byte-array-without-using-an-encoding-byte-by-byte) — Tab Alleman, Feb 29 '16 at 15:30
@TabAlleman Not a duplicate due to the extra step required here for the conversion. `Row.Data.AddBlobData(GetBytes("MyString"));` — JKennedy, Feb 29 '16 at 15:35

score 7 · Accepted Answer · edited May 23 '17 at 12:25

7

using the answer provided for Converting a string to byte-array without using an encoding (byte-by-byte)

I did the following:

Row.Data.AddBlobData(GetBytes("MyString"));

Where:

byte[] GetBytes(string str)
{
    byte[] bytes = new byte[str.Length * sizeof(char)];
    System.Buffer.BlockCopy(str.ToCharArray(), 0, bytes, 0, bytes.Length);
    return bytes;
}

edited May 23 '17 at 12:25

Community

1
1

answered Feb 29 '16 at 15:24

JKennedy

18,150
17
114
198

score 1 · Answer 2 · answered Oct 19 '17 at 19:35

First, you must decide in which encoding you are going to encode your string. If you don't care, I recommend using:

Little Endian UTF-16 for performance, since .NET strings are stored in that encoding
```
System.Text.Encoding.Unicode.GetBytes("MyString");
```

UTF-8 for storage

System.Text.Encoding.UTF8.GetBytes("MyString");

Consider reading more about encoding here: Character Encoding in .NET

You should NOT use the answer provided here . Read the whole discussion there to understand why. The answer is for a very specific use case and a somewhat educational one.

How do I convert a String to a BlobColumn in SSIS

2 Answers2

Linked