Convert String to Bytes

SSIS NText, C# Convert String to Bytes without encoding.

char[] chars = "your string here".ToCharArray();

int char_size = sizeof(char);

byte[] bytes = new byte[chars.Length * char_size];

System.Buffer.BlockCopy(chars, 0, bytes, 0, bytes.Length);

SSIS doesn't support varchar/nvarchar(max), so everything larger than 8000 will get truncated. The datatype that supports very long string is TEXT / NTEXT.

To import a long string from script component (e.g Column Name= NEWROW) and load to database, convert the string to bytes as above and assign it to the NEWROW.

Output0Buffer.AddRow();

Output0Buffer.NEWROW.AddBlobData(bytes);

The NEWROW is TEXT /NTEXT datatype so it doesn't suppor the 'NEWROW.value = string' operation. It is treated as blob data.

Don't use the ENCODING.UTF8/ASCII.GetBytes("your string") to convert your string to bytes. This encodes the string so you will need to decode in the database later, which is hard.