SSIS Script Component DT_TEXT newline not correctly stored in table

Question

I need to store a large text created in the SSIS package Data Flow Script Component to the destination column with data type varchar(max). I use therefore as an Output Column text stream [DT_TEXT]. Inside the C# script I call the method

AddBlobData(Encoding.Default.GetBytes(LARGE STRING WITH LINE BREAKES)) 

All information is stored in the table, however the line breakes are not respected.

I tried different encoding like ASCII, UTF8. Also I have tried to add at the end of every line \r\n, with no change in the result.

StringBuilder sb = new StringBuilder();

sb.AppendLine("This is the first line.");
sb.AppendLine("This is the second line.");
sb.AppendLine("This is the third line.");

DataBuffer.AddRow();
DataBuffer.VarcharMaxColumn.AddBlobData(Encoding.Default.GetBytes(sb.ToString()));

The result:

This is the first line.   This is the second line.   This is the third line.  

There is a huge space between the lines, but not the line break I am expecting to see.

Does anyone knows how to fix this?

Answer 1

I found the answer to my own question. It seems that it is NOT possible to bring over newline from the Data Flow Script Component to the table. Not with DT_STR and not with DT_TEXT. I found this the hard way by spending a lot of time investigating this matter. Although, I hope that I am wrong.

In the meanwhile, I am using a workaround by storing the individual lines as different rows in a temporary table. Afterwards I join them using TSQL to store them in the destination table.

    SELECT T2.[KeyColumn]
          ,(SELECT [VarcharMaxColumn] + CHAR(13) AS [text()]
              FROM [dbo].[TemporaryTable] AS T1
             WHERE T1.[KeyColumn] = T2.[KeyColumn]
          ORDER BY [SortOrder]
               FOR XML PATH(''), TYPE)
          ,T2.[AdditionalColumn]
      FROM [dbo].[TemporaryTable] AS T2
  GROUP BY T2.[KeyColumn], T2.[AdditionalColumn]