CODEWITHSUNDEEP
sql-serverssisetlscript-componentntext
sushu
sushu

Reputation: 47

Load data from Table with column NVARCHAR(MAX) - Cannot convert to string as value is getting truncated

I have a DFT with OLEDB Source - sql table with column named description data type NVarchar(Max)

select max(len(description)) from tbl_Test -- returns 10,000

script transformation editor to perform transformation 

Output0Buffer.AddRow();
        Output0Buffer.TransId = Row.id;
        System.Text.UnicodeEncoding encoding = new System.Text.UnicodeEncoding();
// i am splitting a column here into multiple records - no issues here
        byte[] bytes = Row.descr.GetBlobData(0, (int)Row.descr.Length);
        string myString = encoding.GetString(bytes);
        Output0Buffer.TransDescr = myString;

this code works fine if the output column is DT_WSTR but the problem is that the max here is 8000 hence runtime error that text gets truncated and it indeed will do which i do not want.

If i give output column as text stream DT_NTEXT) the above code gives error.

from this script transformation the destination is a sql table with descr as nvarchar(max).

Upvotes: 2

Views: 698

Answers (1)

Hadi
Hadi

Reputation: 37313

In the script component use DT_NTEXT as data type for the output column, and use the following code.

Output0Buffer.TransDescr.AddBlobData(Row.descr.GetBlobData(0, (int)Row.descr.Length))

References

Upvotes: 0

Related Questions