SSIS – Unicode data is odd byte size for column. Should be even byte size

I'm posting this here as I always forget what the fix is when I run into this error. Massive thanks to LSDBTECH for this fix.

When using a script component in SSIS with a Unicode text stream (DT_NTEXT) output column to an NVARCHAR(MAX) database column you can hit the error:

Unicode data is odd byte size for column <column number>. Should be even byte size

This usually happens because the method for adding the large content takes a byte array argument, and the common way to convert the string to a byte array is using Encoding.UTF8.GetBytes function. The blog post linked above has this explanation:

 “Notice that compressed Unicode strings are always an odd number of bytes. This is how SQL Server determines that the string has actually been compressed, because an uncompressed Unicode string—which needs 2 bytes for each character—will always be an even number of bytes” Source

The fix is to use Encoding.Unicode.GetBytes instead. Here's a code snippet, in this case reading an Azure blob

... (Azure connect stuff goes in here)

var content = blob.DownloadText();

BlobPropertiesBuffer.AddRow();
BlobPropertiesBuffer.Name = blob.Name;
BlobPropertiesBuffer.LastModified = blobProperties.LastModified.Value;

// This next line will cause the error "Unicode data is odd byte size for column 3. Should be even byte size"
BlobPropertiesBuffer.Content.AddBlobData(Encoding.UTF8.GetBytes(content));

// Instead use this line
BlobPropertiesBuffer.Content.AddBlobData(Encoding.Unicode.GetBytes(content));

Also noting that adding to the output buffer column for nvarchar(max)/text streams needs to use the function AddBlobData rather than assigning the value to the column which you do for strings and numeric values (see snippet above)