2

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?

Hadi
  • 36,233
  • 13
  • 65
  • 124
iStan
  • 78
  • 1
  • 11
  • BTW, what are you mean see? use Sql Script select result? this will always show one line if im not mistake, unless you display in rich text. newline is already there but you cant see it, if you load it into Html page, I think you will see it. – BeiBei ZHU Jun 10 '19 at 02:10
  • When performing a SELECT in SSMS you see all in one line inside the cell. However, when you select the value and copy it manually to a text file, I am expecting to have 3 lines. – iStan Jun 10 '19 at 07:20
  • Be careful, ***SSMS does a replace of all new line characters for the display window***. If you want to see new line characters stored in your column, assign the value to a variable, use PRINT and see it on the Messages display. – EzLo Jun 10 '19 at 08:04
  • Same issue here. I am just going to load the data in C# and not use SSIS on this flat file. – KeithL Apr 29 '22 at 20:08

2 Answers2

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]
iStan
  • 78
  • 1
  • 11
0

Try not using a string builder:

string str;
str ="This is the first line." + Environment.NewLine + "This is the second line." + Environment.NewLine + "This is the third line."

DataBuffer.AddRow();
DataBuffer.VarcharMaxColumn.AddBlobData(Encoding.Default.GetBytes(str));
Hadi
  • 36,233
  • 13
  • 65
  • 124
  • Thank you for your answer but it is not working. I tried as you said and also I replaced `Environment.NewLine` with `\n`, `\r` and `\r\n`. I think this must be a bug or something. – iStan Jun 09 '19 at 12:15
  • @iStan try changing the datatype to dt_str and use data conversion transformation to change it back to dt_text – Hadi Jun 09 '19 at 12:18
  • I don't think this is the solution to go. The maximum length of `DT_STR` is 8000 and I need to store information that is most likely more that 8000 characters in a `varchar(max)` column. As I spend a lot of time investigating this issue and my time is limited, I am going for a temporary table storing the individual lines and use TSQL `FOR XML PATH('')` to join them and store them in the destination table. Not a solution, but a viable workaround. – iStan Jun 09 '19 at 12:44